Hi,
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
e.g. if you are doing for a range A2:A2000,
WEEKDAY(A2;2)>5
First cell in the range with relative reference.
@mariosv - As I considered this question as very interesting I tried to find the solution and came up with
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?
First, in condition1 select Formula and in weekday put A1 the first cell of the range.
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.
