VBA to conditionally format a range

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
Hi All again,

I have the following code which I thought will do the job for me. Unfortunately not.
Code:
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
I have also tried this
Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can do this without VBA using standard Conditional Formatting.

But this code should do it for you:

Code:
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
 
Last edited:

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
Thanks for the reply Scott. Unfortunately it is not highlighting anything.
I have managed to get one piece of code working
Code:
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
Code:
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

Well-known Member
Joined
Sep 9, 2008
Messages
1,428

ADVERTISEMENT

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

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
This one worked:
Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
So how can I fix this code to make it work properly? Any suggestions?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
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
Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top