Change the backcolor based on a cell value 1

[tlister82]

I am aware of conditional formatting but I want to take it a step further. How do I change the color of the Cell (not the text) based on a value keyed into that cell? for example if the word "Complete" is entered, change the cell to green, If "pending" is keyed, change the color of the cell to yellow.

Thanks,

Tim Lister
614-692-4371
tlister@cols.disa.mil

 

[DarkSun]

You can change the back color using conditional formatting.

Select the Pattern tab when in the Format window of the conditional formatting.

 

[ratman]

Consider this:
IMPORTANT TO CHANGE THE AscW to the value you need....it is currently set to the word Complete.

PUT THIS IN THISWORKBOOK

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
omgman
End Sub
----------------------------------------------
ADD A MODULE AND PUT THIS IN IT

Sub omgman()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error GoTo endo
a = ActiveCell.Offset(-1, 0).Value
On Error GoTo endo
aADDY = ActiveCell.Offset(-1, 0).Address
newADDY = ActiveCell.Address
b = AscW(a)
If b = 67 Then
Range(aADDY).Select
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With
Range(newADDY).Activate
End If
endo:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

If this doesn't work or if you want to talk email me at
drat@mediaone.net

Ratman

 

[ratman]

oops.....forgot to mention 2 things in the previous reply.

1st-I know a better approach and solution to this entire project but I would need more info to mention it.

2nd-This works when pressing the Enter or Directional Arrow Down key.

That's why I mentioned the better solution earlier.

If this doesn't work or if you want to talk email me at
drat@mediaone.net

Ratman