Change the Color of a Cell through a Macro

[JakeShipley2008]

I am trying to change the color of a group of cells based upon the value of
a different cell. I have the macro below, but it does not seem to work - any
advice.

Thanks,




Sub changecolor()


If r8c1 = AR004328 Then
Range("J10:J189").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

If r8c1 = AR004072 Then
Range("J10:J189").Select
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If
End Sub

 

[Jim Thomlinson]

Try this... or just use conditional formatting...

Sub changecolor()
If range("a8").value = "AR004328" Then
With Range("J10:J189").Interior
.ColorIndex = 1
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

If range("a8").value = "AR004072" Then

With Range("J10:J189").Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If
End Sub

 

[Jim Cone]

Try moving one "End If" to just below the first "End With"
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"JakeShipley2008"
<[email protected]>
wrote in message
I am trying to change the color of a group of cells based upon the value of
a different cell. I have the macro below, but it does not seem to work - any
advice.
Thanks,

Sub changecolor()
If r8c1 = AR004328 Then
Range("J10:J189").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

If r8c1 = AR004072 Then
Range("J10:J189").Select
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If
End Sub

 

[FSt1]

hi
try it with a slight modification.
if all you are doing is changing the back color, you don't need the with
clause.
Sub changecolor()
If Cells(8, 1).Value = "AR004328" Then
Range("J10:J189").Interior.ColorIndex = 1
Else
If Cells(8, 1).Value = "AR004072" Then
Range("J10:J189").Interior.ColorIndex = 38
End If
End If
End Sub

regards
FSt1

 

[JakeShipley2008]

Thanks everyone!! Works like a charm!!
--
Jake

hi
try it with a slight modification.
if all you are doing is changing the back color, you don't need the with
clause.
Sub changecolor()
If Cells(8, 1).Value = "AR004328" Then
Range("J10:J189").Interior.ColorIndex = 1
Else
If Cells(8, 1).Value = "AR004072" Then
Range("J10:J189").Interior.ColorIndex = 38
End If
End If
End Sub

regards
FSt1