Forum

How to highlight a ...
 
Notifications
Clear all

How to highlight a cell if it is greater or less than a column based on its entered data

3 Posts
2 Users
0 Reactions
90 Views
(@a-maurizio)
Posts: 214
Reputable Member
Topic starter
 

Hello everyone my name is A.Maurizio
First of all, too, with a little delay I would like to wish you all a Happy New Year with Friendship and sympathy.

Having said that, my problem is this.
On a sheet of excel in column (B) I want to insert measurements of Body Weight in KG.

While in the row that goes from the cell (I1: N1) I have entered the achievement of its evaluation ranging from "95.0 Kg" you have "70.0 kg"
Now my difficulty in realizing all this lies in this

If I in column (B) enter the figure (96.0 Kg) in a given cell
I would like that in Cell (I1) the "Interior" color was always the same equal to the Base one; As (96.0 Kg) is getting closer and closer to (100.0 Kg)

On the contrary: If the weight of (95.9 kg) or (95.0 kg) is reported in Column (B)
Cell (I1) should turn purple red.

However, in the event that this threshold is exceeded by column (B)
Cell (I1) should return to having the base color
Since the Cell (J1) should be colored
As we move to a lower weight.

that's all !
I of mine tried to do a little something, but with little positive outcome.
Thanks for all the help you want to give me on this.
Greetings from A.Maurizio

I insert test files

 
Posted : 27/01/2021 5:35 am
(@purfleet)
Posts: 412
Reputable Member
 

Rather than VBA cant you use conditional formatting?

If i have understood the requirements correctly, somthing like this

=INDEX($B:$B,MATCH(MAX($A:$A),$A:$A,0))<($I$1-1) (STOP IF TRUE)

=INDEX($B:$B,MATCH(MAX($A:$A),$A:$A,0))<($I$1+1)

Index finds the newest entry, looks up the weight and compares to the cell in I1 - if it within 1 kg it goes red.

i find it quite tricky to get the conditional formatting formula correct, so i think it is easier to do in the worksheet first

Also - please dont use Merged Cells!

Hope it helps

 
Posted : 28/01/2021 5:23 am
(@a-maurizio)
Posts: 214
Reputable Member
Topic starter
 

Hi Purfleet
First of all, thank you for your little help.
Tried it and I have to say it's perfect.
Now I just have to modify it to make the other numbers ready to do the same thing and that's it.
Infinite Thanks Truly.
Sincere Greetings From A.Maurizio

 
Posted : 28/01/2021 12:02 pm
Share: