Problem running row color macro

[cralston]

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

 

[Gary Keramidas]

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

 

[cralston]

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]

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 Keramidas]

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.

 

[cralston]

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]

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

 

[cralston]

Gary,

You da man. Thanks for your help.

Charles

 

[Patrick Molloy]

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

 

[Lost in Alabama]

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