Range: changing interior color

I have a spreadsheet that has info in columns A thru F. I am trying to write
a macro that will background color the row if the cell in column F is
greater than Zero. In other words if in row 11 cell F11 is 5, I want cells A
thru F of row 11 to be colored green. I have tried

If F11 > 0 then Range("A11:F11").InteriorColor = 4

but it doesn't work. What should I do? By the way, I want this to work for
values in rows 11-22. Thanks Jim

Hi Jim,

This can be achieved with conditional formatting.

Select the range to highlight, say A11:Z22
Format | Conditional Formatting
Formula is: =$F11>0
Select a pattern color
OK


---
Regards,
Norman



"Jim Brass" <jbrass@cfu.net> wrote in message
news:dl12r7$6lh$1@news.cfu.net...
>I have a spreadsheet that has info in columns A thru F. I am trying to
>write a macro that will background color the row if the cell in column F is
>greater than Zero. In other words if in row 11 cell F11 is 5, I want cells
>A thru F of row 11 to be colored green. I have tried
>
> If F11 > 0 then Range("A11:F11").InteriorColor = 4
>
> but it doesn't work. What should I do? By the way, I want this to work for
> values in rows 11-22. Thanks Jim
>

Hello Jim,

Change your line of code to...

If F11 > 0 then Range("A11", "F11").Interior.Color = 4

Sincerely,
Leith Ross

You can do this without a macro using conditional formatting:

Select all the cells that the fomatting will apply to (hold and drag
starting from the top row).
Click Edit->Conditional Formatting
Change the dropdown from "Cell Value Is" to "Formula Is" and type "=$F11 >
0" in the textbox
Click the format button and make the background green.
Hit OK then OK again and that's it.


Within a macro (if you have to have it):

Sub SetRangeColor()
For i = 11 to 22
if Range("F" & i).Value > 0 Then Range("A" & i & ":F" &
i).Interior.Color = vbGreen
Next
End Sub



"Jim Brass" wrote:

> I have a spreadsheet that has info in columns A thru F. I am trying to write
> a macro that will background color the row if the cell in column F is
> greater than Zero. In other words if in row 11 cell F11 is 5, I want cells A
> thru F of row 11 to be colored green. I have tried
>
> If F11 > 0 then Range("A11:F11").InteriorColor = 4
>
> but it doesn't work. What should I do? By the way, I want this to work for
> values in rows 11-22. Thanks Jim
>
>
>

I agree with the other responses that you should do this with conditional
formats.

For reference, you're macro would work if you changed it to:

If Range("F11").Value > 0 then Range("A11:F11").InteriorColor = 4

Cheers,

--
Ken Puls
www.officearticles.com


"Jim Brass" <jbrass@cfu.net> wrote in message
news:dl12r7$6lh$1@news.cfu.net...
>I have a spreadsheet that has info in columns A thru F. I am trying to
>write a macro that will background color the row if the cell in column F is
>greater than Zero. In other words if in row 11 cell F11 is 5, I want cells
>A thru F of row 11 to be colored green. I have tried
>
> If F11 > 0 then Range("A11:F11").InteriorColor = 4
>
> but it doesn't work. What should I do? By the way, I want this to work for
> values in rows 11-22. Thanks Jim
>