VBA conditional formatting for rows (more than 3)

[rolly]

I'm trying to use VBA to do conditional formatting for an entire row and I have four conditions, the formatting will change dependent on the text in the "A" column.
I don't want to have to manually run a macro every time something is changed in the "A" column.
I can get it to work great with only 3 conditions but I haven't been able to find code to format the whole row and also format it without manually running a macro each time the text in the cells of column "A" is changed.


The following is part of the macro I recorded to get the correct colors I wanted but I couldn't get much further without errors.
Any help is appreciated.
Thank you,
Rolly


[vba]Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INDIRECT(""A""&ROW())=""Completed"""
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INDIRECT(""A""&ROW())=""In Process"""
Selection.FormatConditions(2).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INDIRECT(""A""&ROW())=""On Hold"""
Selection.FormatConditions(3).Interior.ColorIndex = 45
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INDIRECT(""A""&ROW())=""Cancelled"""
Selection.FormatConditions(3).Interior.ColorIndex = 8[/vba]

[Kenneth Hobs]

If you are using v2003, you can only use 3 conditional formats. If you want 4, you will need to use a worksheet change and/or calculation event. Is the cell for that row in column A changing by a formula or manual entry?

[rolly]

The user will choose from a pick list. I not familiar with a worksheet change or a calculation event, but if either will work, that's fine with me.

[Kenneth Hobs]

Right click the sheet's tab, View Code, and paste this.

Change the tCol to whatever column you like. Also, edit each Case to fit
your cases and your colorindex needs.

I set it to exit if the cell that was changed (target) is not in column A and not more than 1 cell was modified. If you need more than one row set such as a copy paste change for many cells, that is easily done as well.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim tCol As String
tCol = "J"
With Target
If .Count > 1 Or Target.Column <> 1 Then Exit Sub
Select Case UCase(Target.Value)
Case "A"
Range(Target, Cells(.Row, tCol)).Interior.ColorIndex = 1
Case "B"
Range(Target, Cells(.Row, tCol)).Interior.ColorIndex = 2
Case "D"
Range(Target, Cells(.Row, tCol)).Interior.ColorIndex = 3
Case "D"
Range(Target, Cells(.Row, tCol)).Interior.ColorIndex = 4
Case Else
.Interior.ColorIndex = xlColorIndexNone
End Select
End With
End Sub[/VBA]

[rolly]

Thanks for the Worksheet change tip, here's what I have so far, it works, but I still need it to change the entire row, instead of just a single cell.

[vba]Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lastrow As Long
Dim Values As Variant
Dim cell As Variant
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Values = Range("A1:A" & Lastrow)
For Each cell In Values
If cell.Value = "In Process" Then
cell.Interior.ColorIndex = 4
ElseIf cell.Value = "Completed" Then
cell.Interior.ColorIndex = 8
ElseIf cell.Value = "On Hold" Then
cell.Interior.ColorIndex = 44
ElseIf cell.Value = "Cancelled" Then
cell.Interior.ColorIndex = 6
ElseIf cell.Value = "Click to Choose Status" Then
cell.Interior.ColorIndex = 2
End If
Next cell
End Sub[/vba]

[Kenneth Hobs]

You need the Change event rather than SelectionChange. It is inefficient to iterate through all the cells in A. Target tells you which cells were changed. If a cell in A was not changed, there is no reason to contiue.

You can do some forum searches for Intersect for other code snippets.

Notice that I changed column A to J. We could code it to see how many columns had data if needed. While we could change all of one row (Target.EntireRow), that is inefficient and generally looks bad anyway.

Making the changes as I detailed earlier:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim tCol As String
tCol = "J"
With Range(Target, Cells(Target.Row, tCol)).Interior
If Target.Count > 1 Or Target.Column <> 1 Then Exit Sub
Select Case Target.Value
Case "In Process"
.ColorIndex = 4
Case "Completed"
.ColorIndex = 8
Case "On Hold"
.ColorIndex = 44
Case "Cancelled"
.ColorIndex = 6
Case "Click To Choose Status"
.ColorIndex = 2
Case Else
.ColorIndex = xlColorIndexAutomatic
End Select
End With
End Sub[/vba]

[rolly]

Thank you very much, it works great, one last thing I noticed, can you show me how to get this to work if it's looking in Column M instead of Column A?

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range.Columns("M:M")) Is Nothing Then
Select Case Target.Value
Case "In Process"
.Interior.ColorIndex = 4
Case "Completed"
.Interior.ColorIndex = 8
Case "On Hold"
.Interior.ColorIndex = 44
Case "Cancelled"
.Interior.ColorIndex = 6
Case "Click To Choose Status"
.Interior.ColorIndex = 2
Case Else
.Interior.ColorIndex = xlColorIndexAutomatic
End Select
End If
End Sub
[/vba]

[Kenneth Hobs]

M would be column 13 where column A is column 1.
[VBA] If Target.Count > 1 Or Target.Column <> 13 Then Exit Sub [/VBA]

[rolly]

Thank you this is exactly what I needed, I appreciate it.