Cell Color Based on day of week data is entered

Hello,

I have a workbook that I use for inventory return tracking. Each Day I scan Serial Numbers from several different device types into columns. The number of devices scanned is different each day, and different between device types. I highlight the cells based on they weekday the serial numbers were scanned in. I would like to have this done automatically.

For example, when I scan devices Monday they would automatically highlight the Monday color, and the next day when I scan in data it would automatically highlight only those cells with the Tuesday color. And so on for the rest of the week.

Is this possible without having to write a whole new version of Excel in VB? I can usually figure my way around things, but this would be great. Thanks in advance!

Hi bigdatapimp

What is your preferred outcome? I think Conditional Formatting shall do the trick.

1. Highlight the Column you wish to have the format (On my sample, Column I).
2. Select "Conditional Formatting" > "New Rule..." > "Use a formula to determine which cells to format".
3. On the box put "=WEEKDAY(I1)=2" then select "Format" > "Fill" > Select the color you want.

Change the number on the formula according to the days you wish to highlight

Monday is =WEEKDAY(I1)=2
Tuesday is =WEEKDAY(I1)=3
Wednesday is =WEEKDAY(I1)=4
.....

Hope it is what you're looking for.

Thanks for helping. Not quite what I was looking for. The cells I want to have filled are going to be the cells that the data is actually scanned into. Each day I scan serial numbers from each device type into columns A-E. Each Column for a different type. Since there are different numbers for each type everyday they rows will not necessarily line up, so I would need a date column for each data column to do it that way. This would also require me to enter the date for each row, in multiple date columns. Unfortunately that defeats the purpose by creating much more work I am already doing by manually filling the cells.

For example: Column A gets 5 serials scanned, column B gets 9, Column C get 3, Etc.

So what I need is to be able to scan the serials and have the fill for each cell to be the color for that specific day as the data is entered. It really needs to be dependent only on they day of the week the data is originally scanned in. I have tried various different IF statements and conditional formatting and they all either just don't work, or change when I re-open the file the next day. I don't mind implementing VB or scripts, I just have no idea how to do that.

Thanks again for any help

What does your data look like in Columns A - D?
These columns are formatted as Text and your formula =COUNTA(A2:A20) counts
numbers.

When I format Columns A - D to General and put some data in Columns A - D it
seems to work; look at the attached Excel workbook and see if this does what you
want.

* Use the COUNT function to get the number of entries in a number field that is
in a range or array of numbers.

Don't forget to say "Thank You" to those who have helped you in your thread. If
you wish, you can also reward them by clicking on their reputation star bottom
left.

If this resolves your problem then click on Thread Tools at the top and mark it
solved.

The counts for the daily totals work fine. The =COUNTA(A2:A20) would change per day depending on how many serial numbers are scanned. Each day has a total calculator for each row, to count the number of scanned devices. If that could be automated as well that would be fantastic but, that is is not really the issue. What I am trying to do is have the cells be highlighted automatically as the data is scanned, color depending on the day. Hikari was going in the right direction, but not quite what I am hoping for.

to try and clarify further again:

Column A is one device type, Column B is another, and so on. Each day different numbers of devices are scanned for each column. Once each days data is entered, I go back and highlight those cells in each column with the color for that weekday. What I would like is for the sheet to highlight those cell as the data is entered. So one Monday I scan X number of devices in, they are one color. Tuesday I scan another X number of devices and that data gets another color. And so on each day.

I have created Macros to highlight selected Cells for each color, but that still requires me to manually select each range of cells in each column.

Attached is an example of what the sheet could look like.

Thanks again for the Help!

That seems to highlight the Daily Totals. What I am trying to highlight are cells that have the data that is being counted in the totals. The cells in Columns A-D, as I enter the data. so when a blank cell gets data scanned in, it then changes the highlighted color to the color that corresponds with the current weekday.

If it is monday and I scan data into 5 cells in column A, 25 cells in Column B, 17 cells in Column C, and 7 in Column D. I would like each of those cells to automatically highlight one color. Then Tuesday I scan data into 40 cells in column A, 6 cells in Column B, 1 cells in Column C, and 23 in Column D. I would like these cells to automatically highlight another color, without the ones from Monday changing. And So on until Friday. As it is I manually highlight each range of cells in each column after scanning in all the data. If it did automatically as I scan the data in, it make mornings much less time consuming.


Thanks again!

That is kind of what I was thinking. I can think through the logic to make it happen, but I have no idea how to turn that into a formula/script/conditional format. Thanks anyway for trying!

I'm not entirely sure what is going on in that, but I put it in a sheet and it is MUCH closer to what I am looking for. If could change the fill color only when data is entered into the cell that would be great. As it is, any cell that is selected gets highlighted.

Thank you for helping as well!

Even Closer! That might even be what I needed. It doesn't highlight the 1st cell, but that isn't the end of the world. Can you explain how it is controlling the Highlight Color? As of right now, using that script, it is highlighting with the color I normally use for Thursday. As it is, will it be a different color tomorrow?

Thank you again! I have been beating my head against this one for weeks.

That is even closer to what I am looking for! You are a champion among men. With this will the fill color change depending on the day of the week? That is pretty much the only other thing I would like it to do. I don't really do the whole script thing, so I am unsure by looking at it.

Thank you again!

That is Outstanding! Thank you very much. That saves me time, and a ton of frustration trying to figure this out.

Thank you xladept your solution was exactly what I was looking for. In fact I was able to use it in another sheet as well. This will add up to days of saved time in the long Run! I greatly appreciate all the help.