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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,610
Office Version
  1. 365
Platform
  1. Windows
Assuming that the cells are empty you could use something like this, where the variable rng refers to the intersection of the current row and the desired column.
VBA Code:
On Error Resume Next

With Rng
    .Interior.ColorIndex = 0
    With .SpecialCells(xlBlanks)
        Debug.Print .Count
        If .Count < 5 Then .Interior.ColorIndex = 31
    End With
End With

On Error GoTo 0
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
707
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
Sub HighlightCells()
 Dim c As Range
  For Each c In Range("Y2:Y" & Cells(Rows.Count, "Y").End(3).Row)
   If Application.CountA(c.Offset(, 1).Resize(, 5)) Mod 5 = 0 Then
    c.Offset(, 1).Resize(, 5).Interior.Color = xlNone
   Else: c.Offset(, 1).Resize(, 5).SpecialCells(4).Interior.ColorIndex = 31
   End If
  Next c
End Sub
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825
Hi jasonb75 and OsvaldoPalmerio

Thanks for the quick replys. My problem is resolved. Thank you very much.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,254
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad to hear your problem is resolved.
If it was resolved by one of the posts in the thread then you can mark that post as the solution

If it was resolved elsewhere and you would like to post the solution here then it is perfectly fine to mark that post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution. As you will see I have the removed the 'Mark as solution' from post #4.
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825
Glad to hear your problem is resolved.
If it was resolved by one of the posts in the thread then you can mark that post as the solution

If it was resolved elsewhere and you would like to post the solution here then it is perfectly fine to mark that post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution. As you will see I have the removed the 'Mark as solution' from post #4.
Hi Peter, no problem. I ticked the wrong post. Updated now.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,254
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I ticked the wrong post. Updated now.
Easy enough to do. Thanks for fixing it. (y)
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825
Hi, follow up question. Condition 3 has changed slightly in that it needs to colour all blank cells.

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
Change:
3. If the cells in ie row 2 ( Z2, AA2, AB2, AC2 and AD2 ) Have Some blanks then All Blank in the usedrange should have a cell.Interior.ColorIndex = 31

Any help with an amendment would be great.
 

Attachments

  • Image1.png
    Image1.png
    28.3 KB · Views: 6

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
707
Office Version
  1. 365
Platform
  1. Windows
Hi. Try:

VBA Code:
Sub HighlightBlankCells()
 Range("Z2:AD" & Cells(Rows.Count, "Y").End(3).Row).Interior.Color = xlNone
 On Error Resume Next
 Range("Z2:AD" & Cells(Rows.Count, "Y").End(3).Row).SpecialCells(4).Interior.ColorIndex = 31
End Sub
 

Jak

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

Thanks for the follow up. When all of the cells in Z2:AD are blank, it is colouring the cells. Under this condition, they should be xlNone.

If every cell is blank in Z2:AD, Cells should be xlNone
If every cell in Z2:AD has a value, Cells should be xlNone
If the range Z2:AD contains a mix of rows with all blanks, all values and some values, then all the blank cells in Z2:AD will be highlighted.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,699
Messages
5,766,005
Members
425,322
Latest member
galaxy6623top

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