Excel Conditional Formatting - Formula

  • Thread starter Thread starter GDL
  • Start date Start date

GDL

GDL

GDL

Associate
Joined
10 Sep 2014
Posts
430
Location
UK
Hi all.
Quick question.

I have conditional formatting on one cell that looks at a value and gives it a colour based on if it's <, = or> then 0. So:

=Language!I5<0
=Language!I5=0
=Language!I5>0

This applies to one cell =$K$3

My issue is that I want this to apply to =$K$3:$K$42 (easy part) however I cannot get it to inc-count the formatting value with either the format painter or the paste special options. That will increase the range of cells that the formatting applies to but NOT what cell it's looking at to get the value.

so

=Language!I6<0
then
=Language!I7<0

Any ideas?
 
Glancing guess here but your referring to absolute using the dollar. Are you filling the series? Not quite sure what your after.
 
How to I copy the conditional formatting rules downward while changing the formatting value of the conditional formatting (God I hate excel's terminology)

Do this:
Put three conditional formatting rules against cell A1 that looks at cell B1 for a value of either 1,2,3.

Now try to get A2 to do the same with a look up from the value of B2 without redoing the rules by hand. Format painter WILL do it but it will just adjust the range of cells the 3 rules are applied to but they will still look at B1, it won't update what excel calls the 'formatting value' to look at B2. So your left with A2 looking at B1 not B2.

Any clearer?
 
Like this:


xqH6HsN.png



What's important is what you do with those $$ in the formula. The formulas above all reference $b1, that's absolute column b, relative row 1. When one of those formulas are applied across a range , the referenced column will not change (absolute) but the row will increment as you require (relative)
 
You must log in or register to reply here.
Back
Top Bottom