PDA

View Full Version : Solved: Highlight ROW based on cell value



rajkumar
04-08-2009, 10:37 PM
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
04-09-2009, 12:03 AM
Very small change



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

Bob Phillips
04-09-2009, 12:04 AM
Better, do it woithout selecting



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

mdmackillop
04-09-2009, 12:50 AM
A small change to limit the colouring to Column U

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

rajkumar
04-12-2009, 06:45 PM
Hi XLD & MD,

Thanks to both of you. It is working fine. :friends:

Thank you very much

Raj