Worksheet Change event

[Todd Huttenstine]

Hey guys

In column L, the cell value will either be blank, "Yes",
or "No". What I would like is if the cell value is "Yes",
then I need that cell plus the next 10 cells to the left
to have the following format: Black background

I need to have this in the worksheet change event. How
would I do this?


Todd

 

[Frank Kabel]

Hi Todd
why don't you use conditional formating for this?
try the following
- select the cells B1:L20 (or how many rows you want)
- goto 'format - Conditional Format' and enter the formula
=$L1="Yes"
- choose your format

 

[Todd Huttenstine]

I was going to do that at first, but each row depends on
the value of the cell in column L so I would have to
select hundreds of rows and setup conditional formatting
in each. I wish I could drag like I can with a normal
excel formula, but conditional formatting does not allow
this. Because of this I figured a code would be best and
less timely.

 

[Frank Kabel]

Hi
nor problem with that:
just select all rows tigether and enter the formula I provided to you.
Note the mix of absolute and relative reference:
=$L1="Yes"

only change the '1' to the first row of your selection. and of course
you can copy this format for further rows

 

[JE McGimpsey]

You can certainly copy conditional formatting (paste special/formats).
Make sure your cell references are relative (i.e., no "$") for the
references you want XL to change automatically).