Hi guys, I need some help with the following sub. I am trying to write a sub using the "Worksheet_Change" event to be able to add color to the following cells on a worksheet:
* Cells affected: "I14:I23,I26,I40:I49,I52"
* Each cell has a formula that:
+ Displays "INC" if other cells used in formula are empty
+ While the cells are displaying "INC", the cell should remain with no color
+ When cells used in formula have a numerical value, calculation is done
- If calculated value is <95, the cell should turn red
- If calculated value is >105, the cell should turn red
- If calculated value is >=95 and <=105, the cell should turn green
What I have been told that this "Worksheet_Change" event will do, is that each time an entry is made in any of "I14:I23,I26,I40:I49,I52" cells, the subroutine will run to update the affected cells.

Bes regards, Tatooo.

Hello Tatooo,

I would strongly recommend against using the Worksheet_Change event, it'd be better to use Conditional formatting. In fact, you could do all of this with only three conditional formatting rules:
Condition 1:

Cell Value Is | equal to | ="INC"
No Format Set

Condition 2:

Cell Value Is | between | 95 | 105
Format: Green on Patterns tab

Condition 3:

Cell Value Is | not between | 95 | 105
Format: Red on Patterns tab

To set up conditional formatting, just highlight the cells you want to apply these rules to and go to Format | Conditional Formatting... and set the above rules there.

Note that the order you specify the rules does matter, so set them as I have listed above and you'll be good to go.

Regards,

Ax