Excel VBA loop through Conditional Formatting

0
votes

I am seeking help with a conditional formatting VBA code.

What I am basically looking for is to review each row starting from row “H2:Y2” and compare it with the “min” (F2) and “max” (G2) cells and highlight them as per the following conditions/colors:

  • If the value in the cell is between 0 and 50% of the min -> Red
  • If the value in the cell is between 50% and the min -> Yellow
  • If the value in the cell is between the min and the max -> Green
  • If the value in the cell is above the max -> brown

The program should stop once it has reviewed all rows/columns with a value.

Thanks a lot in advance!

Best,

Ugo.

Image table

1
excelvbaconditional-formatting
There is no need to apply the use of VBA as far as I can see. Set the rules on "H2" and then copy paste the formatting across the range you need. - Christofer Weber

1 Answers

1
votes

Example without the use of VBA: Select H2 and create 4 new cond. formatting rules by formula: enter image description here

=H2>($G2) For brown

=AND(H2<=$G2,H2>=$F2) For green

=AND(H2<$F2,H2>=$F2/2) For yellow

=H2<($F2/2) For red

Then you copy H2, select the entire range that you want to apply the cond. formatting on, right click, and select paste formatting. (Or just edit the range in the managing tab for the conditional formatting)
Done.

The trick is to get the $ right, which sets the reference as absolute. So H2in the formula has no $ so this will change as the formula moves.
The min/max columns have a $ in front of the column name, because we always want to look at that column. But none in front of the row number, as we want to go down the rows and use the min/max for those rows.