Solved: Highlight ROW based on cell value

[rajkumar]

Hi Experts,

I need help on highlighting entire row ( till only data available up to last column).

I got a macro from net and i was trying to modify the same. It is not working as i needed. It is highlighting the entire sheet to one particular color.

But i need to highlight only rows that are having cell value as "Break Down" (Column G) to Some Color. My data range is Column A to Column U only.

I do not want to color up any row after column U.

I need to color rows based on the other values on the column G with some other color.

Can anyone help on this - i have attached a sample work book here

Thanks in advance
Raj

[Bob Phillips]

Very small change

[vba]

Sub Macro1()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim LastRow As Long
Dim cell As Range
sSheetName = ActiveSheet.Name

With Worksheets(sSheetName)
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

Range("G2:G" & LastRow).Select

For Each cell In Selection
If cell.Value = "Break Down" Then
cell.EntireRow.Interior.ColorIndex = 39
ElseIf cell.Value = "PM/SM Call" Then
cell.EntireRow.Interior.ColorIndex = 43
Else
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next
End With

End Sub
[/vba]

[Bob Phillips]

Better, do it woithout selecting

[vba]

Sub Macro1()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim LastRow As Long
Dim cell As Range
sSheetName = ActiveSheet.Name

With Worksheets(sSheetName)
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For Each cell In Range("G2:G" & LastRow)
If cell.Value = "Break Down" Then
cell.EntireRow.Interior.ColorIndex = 39
ElseIf cell.Value = "PM/SM Call" Then
cell.EntireRow.Interior.ColorIndex = 43
Else
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next
End With

End Sub
[/vba]

[mdmackillop]

A small change to limit the colouring to Column U
[VBA]
Sub Macro1()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim LastRow As Long
Dim cell As Range
sSheetName = ActiveSheet.Name

With Worksheets(sSheetName)
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For Each cell In Range("G2:G" & LastRow)
If cell.Value = "Break Down" Then
cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 39
ElseIf cell.Value = "PM/SM Call" Then
cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 43
Else
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next
End With

End Sub

[/VBA]

[rajkumar]

Hi XLD & MD,

Thanks to both of you. It is working fine.

Thank you very much

Raj