Formatting a column based on today's date

[markblittle]

I have a spreadsheet I'm using as an order guide for my job. Once my order guide is filled out, I have to manually type the order in a program to send to our warehouse for picking.

Here is an example of how my order guide is laid out:
[TABLE="class: grid, width: 100%, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]ACF[/TD]
[TD="align: center"]ACG[/TD]
[TD="align: center"]ACH[/TD]
[TD="align: center"]ACI[/TD]
[TD="align: center"]ACJ[/TD]
[TD="align: center"]ACK[/TD]
[TD="align: center"]ACL[/TD]
[TD="align: center"]ACM[/TD]
[TD="align: center"]ACN[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TH="colspan: 3, align: left"]Store #[/TH]
[TH="colspan: 3"]9/20/2018[/TH]
[TH="colspan: 3"]9/25/2018[/TH]
[TH="colspan: 3"]9/27/2018[/TH]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TH="align: left"]Description[/TH]
[TH]CC[/TH]
[TH]Code[/TH]
[TH]S[/TH]
[TH]I[/TH]
[TH]O[/TH]
[TH]S[/TH]
[TH]I[/TH]
[TH]O[/TH]
[TH]S[/TH]
[TH]I[/TH]
[TH]O[/TH]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TH="align: left"]Product 1[/TH]
[TH]3[/TH]
[TH]0001[/TH]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TH="align: left"]Product 2[/TH]
[TH]6[/TH]
[TH]0002[/TH]
[TD="align: center"]3[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TH="align: left"]Product 3[/TH]
[TH]12[/TH]
[TH]0003[/TH]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TH="align: left"]Product 4[/TH]
[TH]8[/TH]
[TH]0004[/TH]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TH="align: left"]Product 5[/TH]
[TH]3[/TH]
[TH]0005[/TH]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6[/TD]
[/TR]
</tbody>[/TABLE]

Columns A through C are frozen to the left side while rows 1 and 2 are frozen to the top. As you can see from the column names, I have years worth of orders for reference, as well as a few hundred products to go through in each store I order for.

CC = Case Count. Those with blue text are items I have to order a full case of. Anything in black, I can break cases to meet the needs of the store. Irrelevant to the question.
Code = Order Code
S = Sold
I = Inventory
O = Order

I have been trying to make ordering as efficient as possible and have found that for me, formatting the column for the date of the order (ACH for my last one) to fill any cell greater than 0 is very helpful. Sometimes skimming the guide while inputting my orders, I used to miss an item or two because numbers with rounded shapes would blend in with the 0s before I started doing this. What I would like to do is automate this process, and only format columns for the date of the order.

I fully understand the logic behind it. Find the address or column of the cell in row 1 matching TODAY(), move 2 columns to the right, format that whole column to fill any cells with values greater than 0. A standard formula without using VBA would be ideal if possible.

An added bonus, though entirely unnecessary (and I'm almost positive this would require VBA), would be to also format the item code if my order number is greater than 0.

Thanks in advance,
Mark

 

[Rijnsent]

Hi Mark,
if you use conditional formatting, (option: select a formula):
for your date of 9/20 -> =ACF1=TODAY() -> after that, select that cell and copy-paste formatting to your other dates.
That should highlight the day of today on row 1. Next step is to highlight the zeros:
cell ACH3: =AND(ACF$1=TODAY(),ACH3=0) -> copy that formatting down for the whole column and copy-paste it to every Order column.
Hope that helps,
Koen

 

[markblittle]

Hi Koen,
Sorry for the late reply, but that's ALMOST what I'm looking for. I'm trying to automate this as much as possible, so when my boss updates my order guides to give me the next set of dates and sale information as it comes available, I don't need to go in and edit anything.

Thank you for your response, as it's getting me closer to what I want. I've altered the formula slightly to match my needs. Now I have =AND(A$1=TODAY(),C>0)

It formats the correct rows, but in the wrong column. In the example I provided, if today was September 20, it would format ACF3, ACF5, ACF6, and ACF7. I want it to format ACH3, ACH5, ACH6, ACH7. Is there a way to adjust this formula so that it checks row 1 for the date, but formats two columns to the right given the cell is greater than 0?

 

[markblittle]

Also, I have applied the formatting rule to the entire worksheet, again to prevent having to make any additional changes as I receive updated guides.

 

[Rijnsent]

Hi Mark,
you'd probably just need to "shift" your conditional format. That means: right now your conditional format looks at =AND(A$1=TODAY(),C>0) (assuming that's for column C) -> that means it's looking for the date two columns to the left and the values in the column itself. So you'd probably need to fiddle around a bit with the A and C.
Hope that helps,
Koen