conditional formatting

How do i use conditional formatting to set cells in column C (rows 6 to 20) which have a lower value than C2 to red
GordonMassonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Select the range C6:C20 and use this formula in conditional formatting

=(C6<C$2)*(C6<>"")

choose red fill

regards, barry
barry houdiniCommented:
See example attached, press F9 to generate new random values

regards, barry
26923185.xls
GordonMassonAuthor Commented:
Thanks Barry
Is there a simple change to this that i can make that will use the same formatting for all cells from C6 to N20 but always compare them to the same cell....C2

Thanks
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

barry houdiniCommented:
Yes, Gordon, I used $ in front of the row number so that the row number is fixed, you can fix the column as well, e.g. use

=(C6<$C$2)*(C6<>"")

see revised attachment

regards, barry
26923185v2.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GordonMassonAuthor Commented:
Fantastic Barry thanks a lot!

Can i ask you one last thing.....if i want to turn the formula round and use

=(C6>$C$2)*(C6<>"")

how do i make it > or =
Rory ArchibaldCommented:
Just replace > with >=
:)
GordonMassonAuthor Commented:
Hi Rorya

that makes it only = doesnt it?

I am looking for > or =... larger or equal to
Rory ArchibaldCommented:
Why would replacing > with >= be the same as replacing it with = ?
:)
GordonMassonAuthor Commented:
The reason being, if the cell value that is being used as the main value....C2 in this case is actually equal to the cell we are checking the formating doesnt work if you are using
=(C6>$C$2)*(C6<>"")
but it does work if you are using
=(C6<$C$2)*(C6<>"")
Rory ArchibaldCommented:
=(C6>=$C$2)*(C6<>"")

is what I mean, though you shouldn't need the <>"" check any more, so:
=(C6>$C$2)

Rory ArchibaldCommented:
That last bit should be:
=(C6>=$C$2)
of course!
Rory ArchibaldCommented:
PS Points to Barry, of course.
GordonMassonAuthor Commented:
Great :)
Thankyou both for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.