Results 1 to 2 of 2
  1. #1
    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.

  2. #2
    Join Date
    May 2009
    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.


    Last edited by Ax238; 02-04-10 at 15:23.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts