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!

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" wrote:

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

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

On Tue, 16 Dec 2008 20:50:25 -0000, "MM User" <(E-Mail Removed)> wrote:

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

Thanks Brotha,

I think I've got it!

Regards



"Brotha Lee" <(E-Mail Removed)> wrote in message
news:4431D48A-9C8D-422C-8F77-(E-Mail Removed)...
> 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" wrote:
>
>> 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!
>>

Thanks Gord,

That great!


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> 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
>
> On Tue, 16 Dec 2008 20:50:25 -0000, "MM User" <(E-Mail Removed)>
> wrote:
>
>>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!
>

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!

"Brotha Lee" wrote:

> 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" wrote:
>
> > 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!
> >