Conditional formatting to color only cells in one row

**nivoe** · 10-11-2012, 04:42 AM

hey guys

i am completely new to the forum and i am lost with this problem that i have in excel 2010.
see the attached "Cond_form_date.xlsx" file.

what do i want to achieve?

i want any cell in the column A to turn red whenever i type an x into any cell right of it (so, from column B to ...).
because every column right of the column A represents one day there will be quite some columns (around 1500). that means i reach columns with triple letter length, such as BBB, etc.

my format value is e.g.:
=IF(AND(K1 = "x"; K$1 <> TODAY()); 1; 0)

the AND function is not giving me any headaches. my problem is, that if i choose K1 = "x" and today's date is not today, the column A cells turn red!
SUPER! BUT!

if i write an x to let's say a cell of the column P, it will not color the cell in the column A but the cell in the column F instead, which is exactly the distance from A to K. that means, the cell coloring happens "K cells later".

my first idea was to just get the actual column letter(s) and then it would work out fine. like this:

'current cell letter'1 = "x"

leads to

INDIRECT(LEFT(ADDRESS(ROW(); COLUMN(); 4); MIN(FIND({0,1,2,3,4,5,6,7,8,9}; ADDRESS(ROW(); COLUMN(); 4)&"0123456789"))-1)&1) = "x"

it does not work because apparently one cannot use such functions for conditional formatting.
4 hours wasted, great. but i did not want to give up. tried it with a dirty trick.
with a 2nd worksheet 'rows as row' i tried to not use any MIN etc. functions. that led to

INDIRECT(INDIRECT("'rows as row'"&"!A"&COLUMN())&5) = "x"

now excel did not complain but it still does not work!!!

how come? i do not understand it anymore.
does anyone of you guys know a solution? i would appreciate it a lot!

thanks in advance
nivoe

PS: i do not wish for any VBA solution. that is neither slick nor cool. especially when sharing the document with lots of people with different rights, etc.

**Pete_UK** · 10-11-2012, 05:06 AM

If your requirement is just this:

i want any cell in the row A to turn red whenever i type an x into any cell right of it

then you can apply conditional formatting to column A only using this formula:

=COUNTIF($B2:$Z2,"x")>0

Change the Z2 to something larger if you need to check more columns.

By the way, I think you are getting rows and columns mixed up.

Hope this helps.

Pete

**nivoe** · 10-11-2012, 05:34 AM

hey pete

true, i got rows and columns mixed up

changed it in my first post.

your solution works just great! love it ... did not know excel had such fantastic column functions.
so far i thought excel is pretty weak with columns compared to matlab or R, etc.

now i am trying to make it also dependent on the today's date.
meaning, the cells of the column A should only turn red when there is a x AND TODAY() ...

let me see how well i manage to do that

cheers
nivoe

**Pete_UK** · 10-11-2012, 05:41 AM

Okay, highlight cell A2 down to the bottom of your range, then click on conditional formatting, manage rules, edit rule etc., and use this formula instead:

=INDEX($B2:$Z2,MATCH(TODAY(),$B$1:$Z$1,0))="x"

Note the $ symbols (and change Z to whatever column you are likely to use). Excel will automatically adjust the row reference (2) to suit the other rows that you highlighted to begin with.

Hope this helps.

**nivoe** · 10-11-2012, 05:48 AM

perfect

now, it needs to be even more complex

.
but i am not going to ask you now.

i want to understand your function for the future and try to implement my ideas upon your function.

thank you very much ... i really appreciate your help!

cheers
nivoe

**Pete_UK** · 10-11-2012, 05:54 AM

i want to understand your function for the future and try to implement my ideas upon your function

Essentially, the MATCH function returns the appropriate column for TODAY(), and the INDEX function then looks at that cell in the row, and the condition is that the cell contains "x".

Hope this helps.

Pete