Need VBA for Conditional Format of Row

  • Thread starter Thread starter DanielleVBANewbie
  • Start date Start date
D

DanielleVBANewbie

Hi guys,

I need VBA code to add to an existing macro. If column H has an "x" in it
then I need that entire row highlighed as color 42 (light blue). Below is
what I tried but it isn't working. Any help would be appreciated.

Dim cfrange As range
Set cfrange = range("h7:h700")
If cfrange = "x" Then
range("A:G").Interior.ColorIndex = 42
End If
 
for each c in range("h7:h700")
if ucase(c)="X" then rows(c.row).interior.colorindex=42
next c
 
that did it, thank you so much.
--
Danielle :<)


Don Guillett said:
for each c in range("h7:h700")
if ucase(c)="X" then rows(c.row).interior.colorindex=42
next c

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


.
Click to expand...
 
The only excuse I have for the following is.... it's Saturday and things are
slow.<g>

I decided to investigate whether this problem could be solved without using
a loop and I came up with what follows. Strange thing is that a **quick**
test shows the following code to be equal, or perhaps possibly faster, than
the loop you posted. If anyone has a **high resolution** timer and the
time/inclination to test this out, the results might be interesting. With
that said, here is what I came up with...

Dim Addr As String, Xcol As Range, Col2Color As Range
Set Xcol = Range("H7:H700") 'Range with X's
Set Col2Color = Range("A7:G700") 'Range whose cells can be colored
Application.ScreenUpdating = False
On Error Resume Next
Addr = Xcol.SpecialCells(xlCellTypeBlanks).Address
Col2Color.Interior.ColorIndex = xlNone
Xcol.Replace "X", "", xlWhole, , True
Intersect(Col2Color, Xcol.SpecialCells(xlCellTypeBlanks). _
EntireRow).Interior.ColorIndex = 42
Xcol.SpecialCells(xlCellTypeBlanks).Value = "X"
Range(Addr).Value = ""
Intersect(Col2Color, Range(Addr).EntireRow).Interior.ColorIndex = xlNone
Application.ScreenUpdating = True
 
Back
Top