highlight blank cells if condition is met

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825
Hi all

I an looking for help with VBA as part of a larger macro to loop through each row in a range and based on condition 3, highlight blank cells

The columns to check are "Z : AD" starting at row 2 to the end of the used range

Conditions:
1. If the cells in ie row 2 ( Z2, AA2, AB2, AC2 and AD2 ) are All blank, then the cells.Interior.ColorIndex = 0
2. If the cells in ie row 2 ( Z2, AA2, AB2, AC2 and AD2 ) All Have Values, then the cells.Interior.ColorIndex = 0
3. If the cells in ie row 2 ( Z2, AA2, AB2, AC2 and AD2 ) Have Some blanks then the cells.Interior.ColorIndex = 31

Highlight Blanks.png

Thanks in advance.

Jak
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
707
Office Version
  1. 365
Platform
  1. Windows
Please, try this instead. Hope this time I've followed your criteria . Sorry for that.

VBA Code:
Sub HighlightBlankCellsV2()
 Dim LR As Long, rng As Range
  LR = Cells(Rows.Count, "Y").End(3).Row
  Set rng = Range("Z2:AD" & LR)
  rng.Interior.Color = xlNone
  If Application.CountA(rng) > 0 And Application.CountA(rng) < rng.Cells.Count Then
   rng.SpecialCells(4).Interior.ColorIndex = 31
  End If
End Sub
 
Solution

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825
Hi Osvaldo, that's spot on. The third condition change is working. Thanks for the follow up support.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

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