Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
I created a macro in VBA to color rows based on the value of column K in each
row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
why couldn't you use something like conditional formatting: just change my
range and cell value to what want Range("B8:F8").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="2" With Selection.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.FormatConditions(2).Interior.ColorIndex = 36 End With -- Gary "cralston" wrote in message ... I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
Thanks for the suggestion, but that's not what I'm trying to do. I'm trying
to fill the cell, not do borders. "Gary Keramidas" wrote: why couldn't you use something like conditional formatting: just change my range and cell value to what want Range("B8:F8").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="2" With Selection.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.FormatConditions(2).Interior.ColorIndex = 36 End With -- Gary "cralston" wrote in message ... I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
just use this macro. it will format column e to change to the colors you
want. if k1 = "ok", e1 will be green, if k1 = "Check $", e1 will be yellow, if k1 is an error, e1 will be red. change the range to reflect the column you want Sub Set_Your_Color() ' Range("E:E").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR(K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 End Sub -- Gary "cralston" wrote in message ... Thanks for the suggestion, but that's not what I'm trying to do. I'm trying to fill the cell, not do borders. "Gary Keramidas" wrote: why couldn't you use something like conditional formatting: just change my range and cell value to what want Range("B8:F8").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="2" With Selection.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.FormatConditions(2).Interior.ColorIndex = 36 End With -- Gary "cralston" wrote in message ... I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
forgot to mention. if you change the range to range("K:K").select, it will
actually format the cells your text is in to the colors you want. -- Gary "Gary Keramidas" wrote in message ... just use this macro. it will format column e to change to the colors you want. if k1 = "ok", e1 will be green, if k1 = "Check $", e1 will be yellow, if k1 is an error, e1 will be red. change the range to reflect the column you want Sub Set_Your_Color() ' Range("E:E").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR(K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 End Sub -- Gary "cralston" wrote in message ... Thanks for the suggestion, but that's not what I'm trying to do. I'm trying to fill the cell, not do borders. "Gary Keramidas" wrote: why couldn't you use something like conditional formatting: just change my range and cell value to what want Range("B8:F8").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="2" With Selection.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.FormatConditions(2).Interior.ColorIndex = 36 End With -- Gary "cralston" wrote in message ... I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
Hey Gary,
Thanks so much. One last question, how do I get it to color the row from A:K? I'm used to matlab, not visual basic, which is a lot more compact. Thanks, Charles "Gary Keramidas" wrote: forgot to mention. if you change the range to range("K:K").select, it will actually format the cells your text is in to the colors you want. -- Gary "Gary Keramidas" wrote in message ... just use this macro. it will format column e to change to the colors you want. if k1 = "ok", e1 will be green, if k1 = "Check $", e1 will be yellow, if k1 is an error, e1 will be red. change the range to reflect the column you want Sub Set_Your_Color() ' Range("E:E").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR(K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 End Sub -- Gary "cralston" wrote in message ... Thanks for the suggestion, but that's not what I'm trying to do. I'm trying to fill the cell, not do borders. "Gary Keramidas" wrote: why couldn't you use something like conditional formatting: just change my range and cell value to what want Range("B8:F8").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="2" With Selection.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.FormatConditions(2).Interior.ColorIndex = 36 End With -- Gary "cralston" wrote in message ... I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
i'm sure others here have a better way, but this should work
Sub Set_Your_Color() ' ' Range("a:K").Activate Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR($K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 Range("K1").Select End Sub -- Gary "cralston" wrote in message ... Hey Gary, Thanks so much. One last question, how do I get it to color the row from A:K? I'm used to matlab, not visual basic, which is a lot more compact. Thanks, Charles "Gary Keramidas" wrote: forgot to mention. if you change the range to range("K:K").select, it will actually format the cells your text is in to the colors you want. -- Gary "Gary Keramidas" wrote in message ... just use this macro. it will format column e to change to the colors you want. if k1 = "ok", e1 will be green, if k1 = "Check $", e1 will be yellow, if k1 is an error, e1 will be red. change the range to reflect the column you want Sub Set_Your_Color() ' Range("E:E").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR(K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 End Sub -- Gary "cralston" wrote in message ... Thanks for the suggestion, but that's not what I'm trying to do. I'm trying to fill the cell, not do borders. "Gary Keramidas" wrote: why couldn't you use something like conditional formatting: just change my range and cell value to what want Range("B8:F8").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="2" With Selection.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.FormatConditions(2).Interior.ColorIndex = 36 End With -- Gary "cralston" wrote in message ... I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
Gary,
You da man. Thanks for your help. Charles "Gary Keramidas" wrote: i'm sure others here have a better way, but this should work Sub Set_Your_Color() ' ' Range("a:K").Activate Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR($K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 Range("K1").Select End Sub -- Gary "cralston" wrote in message ... Hey Gary, Thanks so much. One last question, how do I get it to color the row from A:K? I'm used to matlab, not visual basic, which is a lot more compact. Thanks, Charles "Gary Keramidas" wrote: forgot to mention. if you change the range to range("K:K").select, it will actually format the cells your text is in to the colors you want. -- Gary "Gary Keramidas" wrote in message ... just use this macro. it will format column e to change to the colors you want. if k1 = "ok", e1 will be green, if k1 = "Check $", e1 will be yellow, if k1 is an error, e1 will be red. change the range to reflect the column you want Sub Set_Your_Color() ' Range("E:E").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR(K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 End Sub -- Gary "cralston" wrote in message ... Thanks for the suggestion, but that's not what I'm trying to do. I'm trying to fill the cell, not do borders. "Gary Keramidas" wrote: why couldn't you use something like conditional formatting: just change my range and cell value to what want Range("B8:F8").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="2" With Selection.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.FormatConditions(2).Interior.ColorIndex = 36 End With -- Gary "cralston" wrote in message ... I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
Option Explicit
Enum Colors Green = 4 Yellow = 6 Red = 3 None = -4142 End Enum Sub Update_Report_Colors() Dim sheet As Worksheet Dim i As Integer Dim keycol As Long Dim cell As Range Dim found As Range Dim color As Long Set sheet = Worksheets("sheet1") keycol = 11 With sheet Set found = .Columns(keycol).SpecialCells(xlCellTypeFormulas) For Each cell In found Select Case cell.Value Case "OK" color = Colors.Green Case "Check $" color = Colors.Yellow Case "#N/A" color = Colors.Red Case Else color = Colors.None End Select With .Range(.Cells(cell.Row, "A"), .Cells(cell.Row, "J")) .Interior.ColorIndex = color End With Next End With End Sub "cralston" wrote: I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem running row color macro
Gary,
This is Lost in Alabama and I have a question about this code. I am trying to find "01" in column C, select that row and change the color to 45 and loop until all occurences have been found. How would I modify this code to perform this loop? Thanks for your help. Lost In ALabama "Gary Keramidas" wrote: i'm sure others here have a better way, but this should work Sub Set_Your_Color() ' ' Range("A:K").Activate Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR($K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 Range("K1").Select End Sub -- Gary "cralston" wrote in message ... Hey Gary, Thanks so much. One last question, how do I get it to color the row from A:K? I'm used to matlab, not visual basic, which is a lot more compact. Thanks, Charles "Gary Keramidas" wrote: forgot to mention. if you change the range to range("K:K").select, it will actually format the cells your text is in to the colors you want. -- Gary "Gary Keramidas" wrote in message ... just use this macro. it will format column e to change to the colors you want. if k1 = "ok", e1 will be green, if k1 = "Check $", e1 will be yellow, if k1 is an error, e1 will be red. change the range to reflect the column you want Sub Set_Your_Color() ' Range("E:E").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K1=""OK""" Selection.FormatConditions(1).Interior.ColorIndex = 50 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=K1=""Check $""" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=(ISERROR(K1))" Selection.FormatConditions(3).Interior.ColorIndex = 3 End Sub -- Gary "cralston" wrote in message ... Thanks for the suggestion, but that's not what I'm trying to do. I'm trying to fill the cell, not do borders. "Gary Keramidas" wrote: why couldn't you use something like conditional formatting: just change my range and cell value to what want Range("B8:F8").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="2" With Selection.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.FormatConditions(2).Interior.ColorIndex = 36 End With -- Gary "cralston" wrote in message ... I created a macro in VBA to color rows based on the value of column K in each row. It works using a test file with the required values typed in. However, in the file that it NEEDS to work in, those values are the result of an IF function. Here is my code, does anybody have any ideas? I think it probably has to do with the way those values are being generated and not the code... Sub Update_Report_Colors() Dim i As Integer i = 11 ' Will color the cells so long as there is a value entered in column K Do While Not IsEmpty(Columns(11).Cells(i)) If ActiveSheet.Columns(11).Cells(i) = "OK" Then 'sets color to green Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "Check $" Then ' sets color to yellow Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If If ActiveSheet.Columns(11).Cells(i) = "#N/A" Then 'sets color to red Range(Columns(1).Cells(i), Columns(11).Cells(i)).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If i = i + 1 Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem in running a macro from VB6 | Excel Discussion (Misc queries) | |||
macro running problem | Excel Programming | |||
Problem with running macro on Mac computer | Excel Programming | |||
Macro running problem | Excel Discussion (Misc queries) | |||
problem running macro from schedule task | Excel Programming |