can conditional formating be based on the named range?

[tonywatsonhelp]

Hi everyone,

I have a sheet and every cell in range "AA5:AZ500" has its own named range.
then in column E I have a list of some of these named ranges.

is there a formula I could put into conditional formating that would basically say if any cell in range "AA5:AZ500" named range is listed in column E then true?

thanks
Tony

 

[awoohaw]

Just to be clear you have a block of cells AA5:AZ500 (11,700 cells) with each cell being its own named range? So you have 11,700 named ranges with a size of 1 cell each?

You could try something like this User Defined Function:

Function IsTextInNames(LookupRangeName As Variant)
Dim n As Variant
Dim a As Boolean
Dim c As Integer
Dim i As Integer
c = Application.Names.Count
For i = 1 To c
        If i = 1 Then a = False
        n = Application.Names(i).Name
            If n = LookupRangeName Then
                a = True
            End If
    Next i
IsTextInNames = a
End Function

Book1
	A	B	C	D	E	F	G	H	I
1
2							List of Range Names:
3							EUNKN
4	HDIYY	TRUE					TCQFX
5	TCQFX	TRUE					QFIAQ
6		FALSE					HDIYY
7		FALSE
8
9
Sheet1

Cell Formulas
Range	Formula
B4:B7	B4	=IsTextInNames(A4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A7	Expression	=IsTextInNames(A4)	text	NO

 

[tonywatsonhelp]

Thank you awoohaw, yes, as crazy as that sounds its exactly what I have,
and this is a great solution thank you

 

[awoohaw]

My pleasure. Happy to help.

Best Wishes!