Change background based on value in another column

M

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!
 
B

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
 
G

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
 
E

Excel User

Thanks Brotha,

I think I've got it!

Regards



Brotha Lee said:
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
 
M

MM User

Thanks Gord,

That great!


Gord Dibben said:
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
 
T

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top