Forum Discussion

John Bebb's avatar
John Bebb
Copper Contributor
May 03, 2018

conditional format - coluration

Hi Peoples,

Excel "conditional formatting" may be quite helpful - but is INFURIATING to identify the correct syntax to apply such that a "formula" may be construed to funtion correctly...

Col N3 thru Nnnnn may contain a (formulated) text entry of "g" or "r" (or other non-relevant entry)...
Col T3 thru Tnnnn may contain the digit value "1"...

Simple(?) requirement is to colour higlight only the respective col T cells where  Nn= "g" & Tn =1
similar (with different colour) where Nnn= "r" & Tnn = 1

One would assume that =AND(N3="r",T3=1) should suffice - but such does not appear to conform regardless as to where one may include the $ sign...???

Please where can I derive the appropriate syntax rules? - and also - without meaning to appear sarcastic.... when am I going to get a valid reply from "Excel Tech. Com." to my previous question on UNclustering column graphics?
Thank you.

Formulas & Functions
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 03, 2018

    Hi John,

     

    In your case both

    =(T3=1)*(N3="r")   or
=($T3=1)*($N3="r")

    works if you apply it not to entire column T but to range $T$3:$T:$nn. Use absolute or relative references depends on to which range you apply the rule. Conditional formatting takes the rule as it is and apply to the first cell of the range, for each next cell the rule is modified same way as you copy the formula from cell to cell. For T4 it will be checked if (T4=1)*(N3="r") returns TRUE, etc.

     

    If your rule is =($T$3=1)*($N$3="r") it colour all cells in the range depends on what you have in T3 and N3, doesn't matter what in other cells.

     

    Similar if you'd like to colour entire rows, not only cells in column. When absolute references in the rule for column ($T, $N), relative for rows and rule is applied to range like $A$3:$Z$nn.

     

    As for the second question if you attach small sample it'll be much more chances someone answers. To answer people most probably shall generate small model and play with it -your sample will help.

Resources