Automatically Changing the Fill Color Based on a Value to the Right of it

[thefastlane]

Hey,
I was wondering how to set the fill color in a range("A2:C2") based on what C2 said(e.g-"Yes"). I know how to do this step..

If Sheet1.Range("C2") = "Yes" Then
Sheet1.Range("A2:C2").Interior.Color = RGB(0, 176, 240)
Sheet1.Range("A2:C2").Font.Color = RGB(0, 51, 0)
End If

It's automatically doing this for my entire sheet that's confusing. Basically, for every "yes" in column C, I want it and the two cells next to it to be a certain color. Any ideas?

 

[JoeMo]

Hey,
I was wondering how to set the fill color in a range("A2:C2") based on what C2 said(e.g-"Yes"). I know how to do this step..

If Sheet1.Range("C2") = "Yes" Then
Sheet1.Range("A2:C2").Interior.Color = RGB(0, 176, 240)
Sheet1.Range("A2:C2").Font.Color = RGB(0, 51, 0)
End If

It's automatically doing this for my entire sheet that's confusing. Basically, for every "yes" in column C, I want it and the two cells next to it to be a certain color. Any ideas?

Take a look at conditional formatting - no VBA required.

 

[NeonRedSharpie]

I see no issue with your code in isolation. Is this a part of a larger code?

 

[thefastlane]

I initially started with conditional formatting, but ran into the same problem I'm running into now with vba. I have hundreds of rows of data, and it would take forever and a half to code or conditional format each individual row. And no, that's all the code I'm using.

 

[thefastlane]

I know there's nothing wrong with the code, but since I have hundreds of rows of data I'd like to automatically do it.

 

[thefastlane]

And no that's all the code I'm using.

 

[NeonRedSharpie]

I misread your statement. I'm sorry. The code will be:

For x = 2 to 1000 'Change this to your max row
[COLOR=#333333]     If UCase(Sheet1.Cells(x, 3)) = "YES" Then[/COLOR]
[COLOR=#333333]          Sheet1.Range(Cells(x, 1),Cells(x, 3)).Interior.Color = RGB(0, 176, 240)[/COLOR]
[COLOR=#333333]          [/COLOR][COLOR=#333333]Sheet1.Range(Cells(x, 1),Cells(x, 3)).[/COLOR][COLOR=#333333]Font.Color = RGB(0, 51, 0)[/COLOR]
[COLOR=#333333]     End If
[/COLOR]Next x

I thought you were saying the code was making the whole sheet blue and that didn't make sense to me.

 

[JoeMo]

I initially started with conditional formatting, but ran into the same problem I'm running into now with vba. I have hundreds of rows of data, and it would take forever and a half to code or conditional format each individual row. And no, that's all the code I'm using.

You don't need to do each row individually when using CF. Try this:
1. Select the entire range of interest. Let's say that's A2:C1000 for this example.
2. Home>Conditional Formatting> New Rule > Use a Formula
3. formula is: =C2="Yes"
4. Set the fill and font formats you want
5. Click OK

 

[thefastlane]

Don't be sorry! That's perfect! Thanks a lot.

 

[thefastlane]

You don't need to do each row individually when using CF. Try this:
1. Select the entire range of interest. Let's say that's A2:C1000 for this example.
2. Home>Conditional Formatting> New Rule > Use a Formula
3. formula is: =C2="Yes"
4. Set the fill and font formats you want
5. Click OK

Ohh ok! I thought Excel would have a way to do that in CF..