ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Problem running row color macro (https://www.excelbanter.com/excel-programming/335227-problem-running-row-color-macro.html)

cralston
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

Gary Keramidas[_2_]
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

cralston
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

Gary Keramidas[_2_]
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

Gary Keramidas[_2_]
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

cralston
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

Gary Keramidas[_2_]
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

cralston
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

Patrick Molloy[_2_]
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

Lost in Alabama
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


All times are GMT +1. The time now is 12:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com