VBA to conditionally format a range

[asad]

Hi All again,

I have the following code which I thought will do the job for me. Unfortunately not.

Sub Int_Col()

With Range("D15:Q101")
If Range(cell.Row, "A") = "" And Range(cell.Row, "C") = "" Then
Range("D15:Q101").Interior.Color = 192
Else: Range("D15:Q101").Interior.Color = xlNone
End If
End With


End Sub

All I am trying to achieve is to highlight only rows that have cells in columns A and C blank (there may be formulas in those that result in blank).

Any help is much appreciated.
Thanks
Asad

 

[asad]

I have also tried this

Sub Int_Col2()Dim i As Long
For i = 15 To 101
If Cells(i, 1) = "" Then
Range(Cells(i, 4), Cells(i, 17)).Interior.Color = 192
Else: Range(Cells(i, 4), Cells(i, 17)).Interior.Color = xlNone
End If
Next i
End Sub

but this also is not working

 

[Scott Huish]

You can do this without VBA using standard Conditional Formatting.

But this code should do it for you:

Sub Int_Col()
Dim c As Long
For c = 15 To 101
    Cells(c, 4).Resize(, 14).Interior.Color = IIf(Cells(c, 1) = "" And Cells(c, 3) = "", 192, xlNone)
Next
End Sub

 

[asad]

Thanks for the reply Scott. Unfortunately it is not highlighting anything.
I have managed to get one piece of code working

Sub Int_Col4()Dim c As Range
Dim rng As Range


Set rng = Range("D15:Q101")
For Each c In rng
If Cells(c.Row, 3) = "" Then
c.Interior.Color = 192
Else: c.Interior.Color = xlNone
End If
Next c
End Sub

But when I tried to add the second condition, it did not work

Sub Int_Col3()Dim c As Range
Dim rng As Range


Set rng = Range("D15:Q101")
For Each c In rng
If Cells(c.Row, 1).Value = "" And Cells(c.Row, 3) = "" Then
c.Interior.Color = 192
Else: c.Interior.Color = xlNone
End If
Next c
End Sub

 

[asad]

Rows in column A have formulas in them that will result in a number or "" depending on the true or false result of the formula. Is that affecting the code I posted above?

 

[asad]

This one worked:

Sub Int_Col3()Dim c As Range
Dim rng As Range


Set rng = Range("D15:Q101")
For Each c In rng
If Cells(c.Row, 1).Value < 1 And Cells(c.Row, 3) = "" Then
c.Interior.Color = 192
Else: c.Interior.Color = xlNone
End If
Next c
End Sub

But i will wait for your cleaner code Scott.
What I do is all trial and error methods and if something clicks, so be it. But you guys do it properly.

 

[Scott Huish]

That code didn't highlight the row if Column A had a formula blank. I literally put ="" in some of the cells in Column A and it didn't highlight those rows.
Also it will highlight the rows if Column A has negative numbers or 0

 

[asad]

So how can I fix this code to make it work properly? Any suggestions?

 

[Scott Huish]

My code should have worked, it is checking Column A and Column C in rows 15:101 for blank in both cells. Is it actually a blank in Column A or is it a 0 formatted to show as blank??

 

[asad]

Hi Scott,
There are mix of different cells in that range. Some have a formula that plainly refers to another sheet and if the cell in other sheet is blank, then this will be blank. the formula is

=Sheet1!A15

.
Some other cells are there that have no formula or any data in them. They are just empty cells.
I tried your code again, but I cannot get it to work (doesn't highlight any row at all) while the code I have shown above works well with all the cells.