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, 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

Do you think YouTube needs more human reviewers of its videos?

Discuss in The Lounge

Poll History