Conditional Formatting in a range

[imogul]

So I've applied conditional formatting to columns A and B to highlight and bold any blank entries. However since those guys tend to show up a thousand rows or so down I don't have anything to alert me that there is a problem. I know that I can just filter those columns to check but I'm not the only one using this sheet so I'd really like to have a visual cue.

Is there a way to have cell J1 highlight if any of the cells in the range (A2:H3000) have been bolded?

Thanks!

 

[jamtay317]

were you wanting to use a formula? or macro?

 

[perimidt]

Hi

I think a much better approach is to look for blank entries instead of looking for bold ones, and this UDF does just that:

Public Function FindBlanks(RangeWithBlanks As Range)
Dim FirstBlank As String
Dim NumberOfBlanks As Long


For Each cell In RangeWithBlanks
    If cell.Value = "" Then
        NumberOfBlanks = NumberOfBlanks + 1
        If NumberOfBlanks = 1 Then
            FirstBlank = cell.Address
        End If
    End If
Next


FindBlanks = "There are " & NumberOfBlanks & " blanks in the range, the first one in cell " & FirstBlank


End Function

Then put this formula in J1:
=FindBlanks(A2:H3000)

Of if you don't want to use a UDF this formula will count the number of blanks:
=COUNTIF(A2:H3000,"")

Best regards
Per Erik

 

[jamtay317]

what about putting the number or the Item that makes the cells bold in J1
and entering this in conditional formatting
=SUMPRODUCT(--($A$2:$H$3000=$J$1))>0

 

[imogul]

Hey guys thanks for the reply but it's not that easy. See I forgot to mention that I need watch out for Duplicates as well as blanks. Sorry about that.

 

[imogul]

BAM! I figured it out. You guys put me on the right track. I just put in two conditional formats. One to look for blanks using count if "" and one using frequency to find duplicates
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

Thanks for the assist!