Conditional formatting error

I'm not sure why you are using the INDIRECT(ADDRESS … construct - why not just refer to the cell reference directly?

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

The reason I used this was because the formula has to work in conditional formatting, for a wide range of cells. You can't use 'direct reference' because for every column, you are referencing a different cell, and I of course don't want to create a new rule and formula for every column, especially since I have well over 40 of them, and well, if I have to expand the amount of columns to, god forbid, 100+ columns... well, you see the problem.

I'll add the sheet here - I cleaned it out because it contains personal information, so only dates are left. I also added two rows with the formula used in conditional formatting, so you can see that they indeed output TRUE, and despite this, conditional formatting is not working.

I see that your CF rule for TODAY() (i.e. the one that generates an orange background fill) applies to the range =$G$6:$BC$59, so all you need to do is to select Manage Rules on the CF dialogue box with G6 selected, then Edit Rule and change the condition to:

=TODAY() = G$5

When you click OK Excel will automatically adjust the cell reference to suit all the cells in that range.

Similarly for the other condition (blue fill), which also applies to the range =$G$6:$BC$59 - click on Edit Rule and change the formula to this:

=AND(TODAY() =G$5, ISNUMBER(SEARCH("Fast",$D$3)))

When you click OK again the reference will change, and as G$5 is used then the G will change for other columns but the $5 will remain the same on different rows. The cell reference $D$3 will not change.

Hope this helps.

Pete

Mhm, I see your method works, but more importantly:

1. Why doesn't my formula work, even though it is correct? I'm honestly more concerned with that question rather than with it actually working or not.
2. Also, I see it all over the place in tutorials and forums, but why do people always use ',' symbol instead of ';' symbol to end statements for formulas? I mean, in all the versions of Excel I ever worked with, ',' gives an error, and you always have to insert ';'.

Thank you for your help!

I don't know why your construct does not work, as I've never tried doing it that way. It might be that you have to specify the sheet name in the ADDRESS function, as conditional formatting is fussy about that.

The regional settings in Excel determine whether you need to use a comma ( , ) or a semicolon ( ; ) to separate parameters in formulae. In the USA (where Excel originated), and in much of the English-speaking world a full-stop is used to indicate a decimal point in a number, and a comma is used to separate parameters. In much of continental Europe, however, a comma is used as a decimal point, and so a different symbol has to be used to separate parameters in formulae - that is where the semicolon comes in. If you have only ever used Croatian versions of Excel, then you will only have come across semicolons as a list separator.

Hope this helps.

Pete