Change color of cell range based on value of one cell 1

[scriptscribe]

Hi, hopefully someone can help me with this. I have a spreadsheet that I keep on a commonshare network drive where I fix system issues and post them as "Completed" or "Pending." I also color the entire row green when the item is entered to indicate that it is "pending" and change the color to peach when it is "completed." What i want to do is use VBA to automatically change the color of the range of cells (A_:I_) to peach or green when I change the value of column B to "Pending" or "Completed"

Dim wksht as Worksheet
Dim rng as Range
For each rng in wksht
If rng.Value = "Pending" then
A_.color = RGB(0,255,0)
B_.color = RGB(0,255,0)
etc..
I_.color = RGB(0,255,0)
End if
Else
A_.color = RGB(Peach)
B_.color = RGB(Peach)
etc..
I_.color = RGB(Peach)
Next rng

 

[SkipVought]

Hi,

Why don't you use Conditional Formatting -- no code needed! You can Add>> up to 3 conditions.

Menu item Format/Conditional Formatting

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[scriptscribe]

Actually, I tried that first. Getting Excel to color one cell based on the cell's contents is no problem, but I want (for example) A2:I2, or whatever the current row is, to all be shaded in the same color when the contents of B2 is "Pending." Will conditional formatting work on a range of cells like that? If so, any suggestions on how to go about that?

 

[SkipVought]

Select the range you want to format and then do menu item Format/Conditional Formatting.

To Format additional rows, use the Format Painter

use this formula in Conditiuonal Formatting if you are in row 1...

=OR($B1="pending",$B1="complete")

and set your color.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[scriptscribe]

Thanks for your help. Since I needed a seperate color each for "Pending" and "Completed," I did it this way:

=$B1="Pending" Color = Green

=$B1="Completed" Color = Peach