Conditional Formatting based on names in a list.

[xirtam]

I have an issue with conditional formatting

Cell A1 = A
A2 = B
A3 = C
A4 = D

etc

List Cell CA1 = B
CA2 = D
CA3 = F
CA4 = G

I can't get my conditional format to reference the complete list in CA1:CA40 and highlight the cells in A1:A250

I'm not sure what I'm doing wrong.

Please help.

Thanks,

 

[Joe4]

Are you trying to highlight any cells in A1:A250 whose values exist in the range CA1:CA40?

If so, select A1:A250, and then apply this Conditional Formatting formula:

=COUNTIF(CA1:CA40,A1)>0

and select your highlighting color.

 

[xirtam]

Except these are names and not numbers. Is there a way to do that?

Thanks,

 

[Joe4]

Except these are names and not numbers. Is there a way to do that?

Yes, my solution works for that. Did you try it?
As long as the values in the two lists you are looking at are the same type, it doesn't matter if they are text or numbers.

 

[xirtam]

It didn't work until I added $ to the formula to lock in the range, and then it worked perfectly. Kinda odd, but I'm happy with the results. Thank you,

 

[Joe4]

Whoops, yes you need to lock down the lookup range so that doesn't also move as you move down the page, i.e.

=COUNTIF(CA$1:CA$40,A1)>0

Otherwise, the second one would be treated like:

=COUNTIF(CA2:CA41,A2)>0

instead of:

=COUNTIF(CA$1:CA$40,A2)>0

Sorry about that.

 

[ajt8888]

This doent work at all, I have copied the list so the formula is being asked to apply to exactly the same as is in the reference cells and it doesn't do anything other than colour cell a1. I should redo this if I were you.

Whoops, yes you need to lock down the lookup range so that doesn't also move as you move down the page, i.e.

=COUNTIF(CA$1:CA$40,A1)>0

Otherwise, the second one would be treated like:

=COUNTIF(CA2:CA41,A2)>0

instead of:

=COUNTIF(CA$1:CA$40,A2)>0

Sorry about that.

 

[Joe4]

This doent work at all, I have copied the list so the formula is being asked to apply to exactly the same as is in the reference cells and it doesn't do anything other than colour cell a1. I should redo this if I were you.

On the contrary, it does work, and it works quite well! You must not be applying it correctly.

The key is to NOT try to copy the formatting via Copy or Format Painter. Select the whole range you want to apply it to (i.e. A1:A4 in this example), and then write the formula as it applied to the very first cell in that selection (A1). If you use Mixed Range Referencing to lock down the rows, the formula will automatically adjust for the other cells in your selection (see: https://www.excel-easy.com/functions/cell-references.html).

From the exact steps I mentioned in this thread, I have created a macro that sets up the example in this thread and correctly applies the Conditional Formatting. You can run it to see how it works.

Sub TestMacro1()

    Range("A1") = "A"
    Range("A2") = "B"
    Range("A3") = "C"
    Range("A4") = "D"
    Range("CA1") = "B"
    Range("CA2") = "D"
    Range("CA3") = "F"
    Range("CA4") = "G"

    Range("A1:A4").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(CA$1:CA$4,A1)>0"
    Range("A1:A4").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Range("A1:A4").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Range("A1:A4").FormatConditions(1).StopIfTrue = False
    
End Sub

 

[ajt8888]

Apologies now fixed thanks

On the contrary, it does work, and it works quite well! You must not be applying it correctly.

The key is to NOT try to copy the formatting via Copy or Format Painter. Select the whole range you want to apply it to (i.e. A1:A4 in this example), and then write the formula as it applied to the very first cell in that selection (A1). If you use Mixed Range Referencing to lock down the rows, the formula will automatically adjust for the other cells in your selection (see: https://www.excel-easy.com/functions/cell-references.html).

From the exact steps I mentioned in this thread, I have created a macro that sets up the example in this thread and correctly applies the Conditional Formatting. You can run it to see how it works.

Sub TestMacro1()

    Range("A1") = "A"
    Range("A2") = "B"
    Range("A3") = "C"
    Range("A4") = "D"
    Range("CA1") = "B"
    Range("CA2") = "D"
    Range("CA3") = "F"
    Range("CA4") = "G"

    Range("A1:A4").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(CA$1:CA$4,A1)>0"
    Range("A1:A4").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Range("A1:A4").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Range("A1:A4").FormatConditions(1).StopIfTrue = False
    
End Sub

 

[amoverton2]

Are you trying to highlight any cells in A1:A250 whose values exist in the range CA1:CA40?

If so, select A1:A250, and then apply this Conditional Formatting formula:

=COUNTIF(CA1:CA40,A1)>0

and select your highlighting color.

what about a name in a list and another cell that has anything in it (date, words, numbers, etc).

If A1 is in B1:B11 and C1 has anything in it? then highlight A1

Book1
	A	B	C
1	jen	pistol	yes
2	carey	Patrick
3	pete	Joe	1-Jun-21
4	bob	dj	yes
5	phil	Bob	no
6	adam	Phil
7	pistol	Carey	yes
8	jen	Lucky	2
9	ben	Church
10	chuck	Adam	zzz
11		Jen
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A11	Expression	=COUNTIF($B$1:$B$11,A1)>0	text	NO