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, organize...
excel
Formulas and Functions
office 365
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jul 19, 2021

    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.

     

bwy1129's avatar
bwy1129
Copper Contributor
Jul 20, 2021

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.

Resources