• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

conditional formatting based on another rowXcol

LAXMI

New Member
Hi

I am very new to excel


i need to do condtional formatting , i know that since i have cols with values


say(A1:J1)


Y Y Y Y Y N N N N N


i have values in rows X cols as (i.e A5:J10)


10 20 30 40 50 60 70 80 90 10

10 20 30 40 50 60 70 80 90 10

10 20 30 40 50 60 70 80 90 10

10 20 30 40 50 60 70 80 90 10

10 20 30 40 50 60 70 80 90 10

10 20 30 40 50 60 70 80 90 10


now BASED on (A1:J1) whether Y or N and these are subject to change , these are dynamic

for example the row might be random like this

Y Y Y Y Y N N Y N Y

OR

ANYTHING


N N Y N Y N N N N N

and so on...

I want the cells to be formatted with say yellow for Y and green for N

note: let me also explain that the values inthese cells are dynamic they are based onthe programming behind and the no of rows may change


the columnrange remains the same though

please advice with an example so it teaches me to hanldle diff other situations in next projects

all help is appreciated

thanks
 
Let's say you select range A5:J10, with A5 being the active cell (cell you are currently looking at)


Conditional format formula 1 is:

=A$5="Y"

Format w/ yellow pattern


Formula 2:

=A$5="N"

Format w/ green pattern


Ok out.


That's it! If you need to, you can copy/extend the formatting to other cells. The trick is that both formulas say to look at a "relative" column (in this case, the same column as the cell) but look at the "absolute" row 5 (indicated by the dollar sign).
 
Back
Top