Change background based on value in another column

[MM User]

Hi,

I have in column A options: a,b,c or d

depending on what is chosen is it possible change the background color of
the relevant row in B and C?

i.e. if a3 = a then b3 & d3 background is red or
if a3 = b then b3 & d3 background is green etc

If possible is it possible to do this automatically i.e. not run a macro
just on a cell change in A?

Thanks!

 

[Brotha Lee]

MM,

You could use regular Excel functionality like conditional formatting (under
format menu), however if you desire a code use the following. You should
paste it somewhere in the appropriate sheet object
Private Sub Worksheet_Change(ByVal Target As Range)
'Use Lcase(Target) if it should not be case sensitive
Select Case Target
Case Is = "a"
Cells(Target.Row, 2).Interior.Color = vbRed
Cells(Target.Row, 3).Interior.Color = vbRed
Case Is = "b"
Cells(Target.Row, 2).Interior.Color = vbGreen
Cells(Target.Row, 3).Interior.Color = vbGreen
Case Else
'No appropriate entry, clear color
Cells(Target.Row, 2).Interior.ColorIndex = xlNone
Cells(Target.Row, 3).Interior.ColorIndex = xlNone
End Select

End Sub

HTH
Brotha lee

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim iColor As Long
Set R = Range("A1:A100")
If Intersect(Target, R) Is Nothing Or Target.Count > 1 Then Exit Sub
Vals = Array("A", "B", "C", "D")
Nums = Array(8, 9, 6, 3)
For i = LBound(Vals) To UBound(Vals)
If UCase(Target.Value) = Vals(i) Then iColor = Nums(i)
Next
With Target
.Offset(0, 1).Interior.ColorIndex = iColor
.Offset(0, 3).Interior.ColorIndex = iColor
End With
End Sub

Note: you could do this with Conditional Formatting.........default color
for a and three others for b, c, d

If using Excel 2007 you have many more conditions.


Gord Dibben MS Excel MVP

 

[Excel User]

Thanks Brotha,

I think I've got it!

Regards

MM,

You could use regular Excel functionality like conditional formatting
(under
format menu), however if you desire a code use the following. You should
paste it somewhere in the appropriate sheet object
Private Sub Worksheet_Change(ByVal Target As Range)
'Use Lcase(Target) if it should not be case sensitive
Select Case Target
Case Is = "a"
Cells(Target.Row, 2).Interior.Color = vbRed
Cells(Target.Row, 3).Interior.Color = vbRed
Case Is = "b"
Cells(Target.Row, 2).Interior.Color = vbGreen
Cells(Target.Row, 3).Interior.Color = vbGreen
Case Else
'No appropriate entry, clear color
Cells(Target.Row, 2).Interior.ColorIndex = xlNone
Cells(Target.Row, 3).Interior.ColorIndex = xlNone
End Select

End Sub

HTH
Brotha lee

 

[MM User]

Thanks Gord,

That great!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim iColor As Long
Set R = Range("A1:A100")
If Intersect(Target, R) Is Nothing Or Target.Count > 1 Then Exit Sub
Vals = Array("A", "B", "C", "D")
Nums = Array(8, 9, 6, 3)
For i = LBound(Vals) To UBound(Vals)
If UCase(Target.Value) = Vals(i) Then iColor = Nums(i)
Next
With Target
.Offset(0, 1).Interior.ColorIndex = iColor
.Offset(0, 3).Interior.ColorIndex = iColor
End With
End Sub

Note: you could do this with Conditional Formatting.........default color
for a and three others for b, c, d

If using Excel 2007 you have many more conditions.


Gord Dibben MS Excel MVP

 

[Toypon]

Thank's Brotha Lee!

I have another function I want to add to this if anyone could help me.
I use a locked sheet with some unlocked cells and based on the value in A2
the cell B2, C2 aso should change between locked and unlocked along with the
background.

How do I do this? I Use Brotha Lee's example of changeing background.

Thanks in advance!