Hi All,

Sorry if this is a bit of a muggle question, but i'm still getting my head round VBA.

I've got various macros at the moment that highlight certain things given a criteria, but so far i've only been able to highlight the entire row which is quite messy. The worksheet goes from column A - N and thats all i'd light highlighted. The code i've got so far is;

Sub HighlightRed()
Dim cell As Range

Range(Range("L7"), Range("L7").End(xlDown)).Select
For Each cell In Selection
If cell = "Red" Then cell.EntireRow.Interior.ColorIndex = 3
Next cell
End Sub

So it looks in Row L (starting from cell 7), and looks for any cells with "Red" in them, and highlights that entire row red. However I only want it to highlight cells in rows A to N, and not the "EntireRow".

Any help would be appreciated,

Thanks,
Ed

Hi there

Try this

Sub ChangeCellColor()

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long

With Sheets("Sheet1")
.Select
Firstrow = .UsedRange.Cells(7).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "L").Interior.Color = 255 Then
Cells(Lrow, 1).Resize(Lrow, 14).Interior.Color = 255
End If

Next Lrow

End With

End Sub

HTH
Mick.

You could accomplish this without VBA by using conditional formatting. Just highlight columns A:N and click conditional formatting, new rule. Note the active cell (in this example it is A1), and enter this formula

=$L1="Red"

By locking column L but leaving the row relative, the cells in a given row (columns A:N) will be highlighted based upon the value in column L.

Hope this helps

Ben