Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Highlight Selected Range Only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Selected Range Only
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Selected Range Only
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight selected cells only | Excel Worksheet Functions | |||
Highlight selected area | Excel Discussion (Misc queries) | |||
How do I have a cell highlight when selected? | Excel Discussion (Misc queries) | |||
Trying to highlight (bold,red) selected text within range | Excel Discussion (Misc queries) | |||
Highlight Row when selected | Excel Discussion (Misc queries) |