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

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

--
HTH...

Jim Thomlinson


"JakeShipley2008" wrote:

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

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"

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

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

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

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


"FSt1" wrote:

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

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