Formula for conditional format to color cells

first of all, thank you. you are very very good at what you do. i appreciate the help. i don't understnad how to write comlicated formulas and you are providing a tremendous abount of help. teach me how to write formulas,and i will stop bothering you. ha ha.

moving one. that last View Code formula didn't work. gave me many different errors.

let me better explain. my header is k63 - t63. cells below each header cell are k64-k76. then l64-l76, then m, n, o, p, etc...until t64-t76.

at the start of each day, the header cells should be cleared/empty and auto filled w/red. the cells below are empty w/out a fill color. the idea is to "fill in the red header cell w/a name." that cell then removes the red auto fill color leaving no auto fill color but only a name.

cells below the header are were empty and no auto fill color. however, w/the name dropped into the headers cell, the cells below remain empty but turn red.

after a number is dropped into each individual cell below the header cell, those cells individually auto fill w/out a color but do include the various numbers.

I think this is the formula you created last week.

now, at the beginning of the day or end of the day when the spreadsheet is initially opened, the header cells revert back to being empty and auto fill w/red removing all numbers below as well. reverting those cells back to no auto-fill color.

this will apply to k63-t63 (header) and k64-k76 and l, m, n, o, p.....across to t.

the idea is to indicate to the user "fill in the red cells" with either a name. then when the cells below the "name" turn red, fill in those cells. it's a navigation guide for users.

if you can create a formula that will clear yesterdays data once the spreadsheet is opened at the beginning of each day, that would be a HOME RUN.

I have many cells on another spreadsheets that I want to create a formula that clears all of last months data, leaving blank "x" cells, to start filling in for a new month.

I have a HUGE need for a formula that says "if that cell is blank, then make this cell auto fill with RED, with a number, etc...

again, thank you so much. good luck.

Not sure what you need help with. You know how to setup the conditional formatting and you got a code to clear the values beneath the "headers" once they get cleared.

"I have many cells on another spreadsheets that I want to create a formula that clears all of last months data, leaving blank "x" cells, to start filling in for a new month".
Which cells are we talking about? I would create a button for that. Once clicked it will clear the designated cell.

"I have a HUGE need for a formula that says "if that cell is blank, then make this cell auto fill with RED, with a number, etc... "
Not sure what number it needs to be and what you mean by etc., but the first two are done with CF.

Best regards,
Trowa

I'm becoming a bother. I'm sorry. last question and I will move one to other challenges. thank you for your help. last question....how do I create this "button" to clear designaged cells? eg, I want to click on this "button" to clear cells a1-a15, b1-b15, c1-c15, d1-d15 and so on..... again. thank you.

sorry Trowa. that didn't work. i tried to follow your reply step by step. some of your titles I was not able to see w/in my excel version. they didn't exists. i may have a different version of office than you. i have excel 2016.

let me try posing the question a different way. I want to start the day w/yesterday's data removed from a wide variety of cells. e.g., f6-f13, e15, e16, f15, f16, f19, h18, k64 - t64 (and all data w/in cells k65-k77 - t65-t77 plus all data w/in those ranges).

is there a way to create a command to hit, enter, access, etc..., to "clear" yesterday's data w/in those cells and many more? you may have already given me the answer to this question 5.28.19. if so, that didn't work. I will continue trying until you have time to reply.

thank you SO VERY much.

I tried again. I received this message when I clicked on my new box.
in the foreground:
"Microsoft Visual Basic for Application...
Compile error:
Expected: list separator or )

in the background:
CCTC State (County/Clerk Certified Copy)
this is language from another tab w/in my spreadsheet. I deleted the work "Certified" b/c is was highlighted. I closed the error message box and was told "this command will stop the debugging." I exited and clicked on my new box again, and the same message populated highlighting another word. I repeated this process again and again. all it's doing is choosing another work from that same tab.

I got it Trowa. I got it. Amazing. check out this video and this is what I was after. https://www.youtube.com/watch?v=lfSIzR-Lja8
this is I'm SURE what you were trying to explain. I know what I want in my head, but have a challenge putting it to paper. thank you SO much. you helped out on both my formula challenges and I can't thank you enough. I will be back later I'm sure to ask more questions. for now. cheers. thank you. good day. kipp

Awesome Kipp. Nice find. The video used a different way to run the macro, a shape instead of a command button, but the result is the same. Until next time.

I noticed once you hit the shape or now I have assigned a keyboard shortcut (Ctrl h) to run my macros, you cannot reverse this action. do you know a way to apply a warring message to the shape or to the keyboard short cut command? or a assign a password to this shape or keyboard shortcut command?

I'm trying to make sure the user intended to click on this shape or intended to use the keyboard shortcut. Something like "Warning Will Robinson; Warning..." lol. I hope I explained this correctly.