Highlight Selected Range Only

[TesterEd]

Hi All,

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

I've got various macros at the moment that highlight certain thing
given a criteria, but so far i've only been able to highlight the entir
row which is quite messy. The worksheet goes from column A - N and that
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 cell
with "Red" in them, and highlights that entire row red. However I onl
want it to highlight cells in rows A to N, and not the "EntireRow".

Any help would be appreciated,

Thanks,
E

 

[Living the Dream]

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.

 

[benmcclave]

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