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

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

DerbyDad03, simply - you rock! Cant thank you enough.

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?

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

Hi DD03, thanks for the follow up. I appreciate that you do this for the benefit of others.

Well done, great brain!

Ask Your Question

Weekly Poll