Solved Can I use CF to highlight a cell colour based on row content

January 10, 2018 at 02:46:50
Specs: Macintosh
If there is the numeral 1 somewhere in a row from say, N5 to N50, can I use a formula to turn the N1 cell red in colour?
Any help would be great, thanks in advance:)

message edited by Eskylid


See More: Can I use CF to highlight a cell colour based on row content

Report •

#1
January 10, 2018 at 03:53:15
✔ Best Answer
I can't test this right now, but you should be able to use COUNTIF to accomplish your goal.

Select N1, open the CF tool, chose "Use a formula..."

=COUNTIF(N5:N50,1)>0

Chose your formatted.

I will test this later and repost if there is an issue.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
January 10, 2018 at 03:59:14
DerbyDad03, simply - you rock! Cant thank you enough.

Report •

#3
January 10, 2018 at 04:16:04
DerbyDad03, Hi again. I've been busily working away on my spreadsheet since you gave me a new lease of life with the solution to the previous problem.
Can I ask if the cells in the range in question have contents such as "1-BARRY" or "2-ZAC" in them, can I use a similar formula as before, but get it to discount / ignore the text and just return formatting based on the numeral only?

Report •

Related Solutions

#4
January 10, 2018 at 06:27:42
BTW, values in N5:N50 aren't "in a row", they are actually "in a column".

Try this as your Conditional Formatting formula.

=SUMPRODUCT(--(LEFT($N$5:$N$50,1)="1"))>0

Just in case you didn't know this:

When using a formula in CF, you need to use a formula that evaluates to TRUE or FALSE. When the formula evaluates to TRUE, the format will be applied.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
January 10, 2018 at 14:24:45
Hi DD03, thanks for the follow up. I appreciate that you do this for the benefit of others.
Well done, great brain!

Report •

Ask Question