Sleeper: How to color code a row if range of columns are blanks

[lalani123]

Hi Experts,
I would like your help to color code a range of cells for each row if more than 4 columns are blank.

Product | Month 1 | Month 2| Month 3| Month 4| Month 5| Month 6
ABC| | | | 500 | 110 | 90
XYZ|10|25||||
DEF|5|||||
UVW|10||||50|

I want to check for each row, if last 4 months (Month 3 to 6) are blank, color code them in a different color. Also, if a longer range is blank that includes the last 4 months, color code them to. For ex. row DEF has empty cells for Month 2 to Month 6, in the case i want to color code month 2 to month 6 and not month 3 to 6.

[SamT]

Try this in the Worksheet's Code page
Sub VBAX_SamT_ColorEmptyMonths()
Dim rw As Long
Dim Cel As Range
Dim WsF As Object
Set WsF = Application.WorksheetFunction

For rw = Range("A1").CurrentRegion.Rows(1).Row + 1 To Range("A1").CurrentRegion.Rows(Rows.Count).Row
If WsF.CountA(Range("B" & rw & ":G" & rw)) = 0 Then
Range("B" & rw & ":G" & rw).Interior.ColorIndex = 3
ElseIf WsF.CountA(Range("D" & rw & ":G" & rw)) = 0 Then
Range("R" & rw & ":G" & rw).Interior.ColorIndex = 3
Else: Range("B" & rw & ":G" & rw).Interior.ColorIndex = xlColorIndexAutomatic
End If
Next rw
End Sub

[lalani123]

Hi SamT,
I included the code in button click event. It highlighted all the rows in that sheet (regardless of how many rows had data).
Rows which had no data had the color coding from Column B to G.
Rows which had data had color coding from had one blank columns after the last column with values and then the red color coding in cells.

[SamT]

Please upload the sheet for us.

[jolivanes]

Try this while you get an attachment example ready.



Sub Colour_Empties()
Dim lc As Long, lr As Long, j As Long, luc As Long
lc = ActiveSheet.UsedRange.Columns.Count
lr = Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To lr
luc = Range("XFD" & j).End(xlToLeft).Column
If lc - luc > 3 Then Range(Cells(j, luc + 1), Cells(j, lc)).Interior.ColorIndex = 3
Next j
End Sub