Ask Your Question

"this cell" in conditional formatting

asked 2013-07-07 07:21:22 +0100

Harry2o gravatar image


How do I use "this cell" in conditional formatting? E.g. I am trying to add a WHOLE column as date (every row is one day, many years in total) and would like to see the weekends. So I'm trying to put conditional formatting for the whole column and use the formula WEEKDAY(;2)>5, but what do I put as cell reference? I want each cell to calculate the formula with its own content, i.e. a date.

Thanks ~Harry

edit retag flag offensive close merge delete


e.g. if you are doing for a range A2:A2000, WEEKDAY(A2;2)>5 First cell in the range with relative reference.

m.a.riosv gravatar imagem.a.riosv ( 2013-07-07 13:31:59 +0100 )edit

@mariosv - As I considered this question as very interesting I tried to find the solution and came up with ![image description](/upfiles/13731996237722092.png) Data range is A1:A43 and filled with dates formated NN, d, mmm, yyyy. Could you please let me know shat is wrong in my conditional formatting?

ROSt52 gravatar imageROSt52 ( 2013-07-07 14:27:09 +0100 )edit

First, in condition1 select Formula and in weekday put A1 the first cell of the range.

m.a.riosv gravatar imagem.a.riosv ( 2013-07-07 15:47:59 +0100 )edit

@mariosv - Thanks for hint. It works!

ROSt52 gravatar imageROSt52 ( 2013-07-08 10:47:28 +0100 )edit
Tim Abell gravatar imageTim Abell ( 2017-03-12 12:22:04 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-01-16 21:16:54 +0100

LogicDaemon gravatar image

updated 2015-01-16 21:25:29 +0100

you put the cell your cursor in (bold cell borders) as "reference", which will be extended to all selected range. It works same way as writing formulae for one cell, then copying the cell to whole range.

If your cursor was in A2, you put A2 in your formulae. If your cursor was in A1000, you put A1000, and for A2 conditional formulae will reference A2.

You can even format other cells that way, so you can, say, format whole line if weekday in column A is >5. To do so, select whole range, put cursor to A2, and add conditional formatting with =WEEKDAY($A2;2)>5 (note $ before A, so cells to the right from your cursor sill reference column A).

Same way, you can colour cells depending on data in other ranges, even other sheets.

Note there is inconvenience: If, when adding conditional formatting, cursor isn't on-screen, you both can't scroll to see its position, and you can't read it from field left from formulae bar (because there will be selected range instead). I don't know how to avoid closing "conditional formatting" window in that case.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools


Asked: 2013-07-07 07:21:22 +0100

Seen: 6,284 times

Last updated: Jan 16 '15