02-04-10, 14:16 #1Registered User
- Join Date
- Sep 2009
Unanswered: Worksheet_Change(ByVal Target As Range)
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.
02-04-10, 14:57 #2Registered User
- Join Date
- May 2009
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:
Cell Value Is | equal to | ="INC"Condition 2:
No Format Set
Cell Value Is | between | 95 | 105Condition 3:
Format: Green on Patterns tab
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.
Last edited by Ax238; 02-04-10 at 15:23.