Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change the Color of a Cell through a Macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change the Color of a Cell through a Macro
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change the Color of a Cell through a Macro
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change the Color of a Cell through a Macro
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change the Color of a Cell through a Macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change a cell color based on its content using macro? | New Users to Excel | |||
Macro to change cell color | Excel Discussion (Misc queries) | |||
Macro To Change Cell Color (Continued) | Excel Worksheet Functions | |||
Macro To Change Cell Color When Value Changes | Excel Worksheet Functions | |||
Macro to change cell text color | Excel Discussion (Misc queries) |