Formatting a column based on today's date

markblittle

New Member
Joined
Sep 20, 2018
Messages
3
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,284
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top