Forum Discussion

bwy1129's avatar
bwy1129
Copper Contributor
Jul 19, 2021
Solved

Creating conditional formatting in cells OTHER than the selected cell

I'm a diabetic and am working on a spreadsheet to keep track of insulin needs. The data (both collection, ie: unlocked cells) and calculation (ie: locked cells) is spread across columns A:P, organized by date on "relative line" 1, and covers "relative lines" 1:11. Because so much acreage is being covered EACH DAY, it is easy to loose track of "today" data among all the other data. I thought that perhaps I could conditionally format an entire relative line 1 (ie: A1:P1) when the date in B1 = today(). Well, I can do that easily in cell B1, but doing it in cells A1:P1 (rolls eyes) or worse, in A1, B1, C1, D1, ..., or even worse still in A1, B1, C1, D1,...
A12, B12, C12, D12...
...
A4015, B4015, C4015, D4015...  is a nightmare.

Here is what I want to do, in the steps I've taken to attempt to do it:
Step 1, select "Conditional Formatting"
Step 2, select "Manage Rules"
Step 3, select "In worksheet"
Step 4, select the rule that I wish would work correctly
Step 5, select "Edit rule"
Step 6, edit the formula to:

=if(cell("content",address(cell("row"),2,3))=today(),true,false)


{Translation: if the contents of a cell on the same row as the currently selected cell, but in column B instead of the currently selected cell's column, contains a value equal to the value produced by the "TODAY()" function, then format the currently selected cell with the defined format.}

Step 7, create the formatting for background color and text color to highlight the start of the current day's data.

The problem is that Excel generates the error, "There's a problem with this formula. Not trying to type a formula? When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a formula: • you type:   =1+1, cell shows: 2. To get around this, type an apostrophe ( ' ) first: • you type:   '=1+1, cell shows =1+1 [  OK  ]  [  Help  ]

I've tried it with the beginning =.
I've tried it without the beginning =.
I've tried it with the beginning apostrophe, followed by the =.
I've tried it with the beginning apostrophe, without the following =.
I've tried it in a cell as a cell formula, completely outside of the Conditional Formatting Rules.

What am I doing wrong?

  • bwy1129 Not sure if I understood but I believe you can use the following formula to set the CF-rule:

     

    =$B1=TODAY()

     

    and apply it to the entire data range, e.g. $A$1:$P$4015

     

    This should highlight the entire row, for each row where the date in column B equals today's date.

     

    A working example is attached.

     

2 Replies

  • bwy1129's avatar
    bwy1129
    Copper Contributor

    Thanks to Riny_van_Eekelen for solving my issue. I couldn't see the trees for the forest. I was using ...$B$4... in my formula and he used $B4 in the sample formula he sent back.

    I've attached a screenshot of the spreadsheet so if anyone is curious what I was trying to resolve, you can see why I needed highlight where TODAY'S data was/is.

    In the screenshot, I've notated five areas. By the numbers, they are:
    1. Pale straw-colored fields are data input fields.
    2. Pale sky-colored fields are calculated results and end-of-data information most diabetics need to know.
    3. Pale dusty rose-colored fields are warning type data and high alert messages.
    4. Strong green-colored row is the start of the TODAY data.

    5. The size of the spreadsheet that a single day's data occupies.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    bwy1129 Not sure if I understood but I believe you can use the following formula to set the CF-rule:

     

    =$B1=TODAY()

     

    and apply it to the entire data range, e.g. $A$1:$P$4015

     

    This should highlight the entire row, for each row where the date in column B equals today's date.

     

    A working example is attached.

     

Resources