hi,
need a formula or conditional format:
if cell a1 has text, then apply the color RED to cells underneath say a2-a15.
then, once numbers are inserted in the cells a2, a3, a4, etc, those cells keep the numbers but turn the cells back to no fill or white.
Sure, but when A1 is empty then A2:A15 will be changed back the white cell as per the conditional format rule.
Right-click your sheets tab and select View Code and paste the following in the big white field:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
If Target.Value = vbNullString Then Range("A2:A15").ClearContents
End Sub
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...
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.
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.
I don't want you to feel like you are a bother, because you are not! I just need to understand you, before I can help.
Since you put the word button in quotes, I'm assuming you are not familiar with it.
To create a button you will need the Developers ribbon.
1.When it is not visible, right-click any other ribbon tittle, for example Start.
2.Choose to adjust the ribbon.
3.On the window that pops up, find the Developers check box in the right side window and check it.
4. Click OK.
5. On the Developer ribbon click on Insert.
6. The small popup is divided in 2 parts: Form (top) and ActiveX (bottom).
7. In the ActiveX part find the Command button (top left).
8. Once you click it, you can draw your button. Holding ALT while doing so will allow your button to match the cells grid.
9. Right click your button and select properties.
10. Here you can adjust your button. The ones I change are usually: BackColor (change the color of the button), Caption (the text on the button), Font (change the format of the text).
11. Once your done, close the adjusting window and right click the button again and now select View code.
12. 2 lines are already given. Paste the line below between the 2 lines:
Range("A1:T15").ClearContents
13. Back at Excel, have a look at the Developers ribbon and next to Insert you will find Design mode. Click it so it is not selected anymore.
Now you can click your button to clear the contents of range A1:T15.
Hopefully my explanation/directions are easy to follow.
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.
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.
myWarning = MsgBox("Action can NOT be reversed using the blue arrows!" & vbNewLine & vbNewLine & "Click OK to continue or Cancel (or hit Esc or close this message) to abort.", vbOKCancel, "WARNING WARNING WARNING WARNING")
If myWarning = vbCancel Then Exit Sub
Ctrl + h is used by Excel for Find & Replace, so you might want to reconsider the shortcut you chose.
I'm using a Dutch version and tried my best to translate. It would help if you could mention the steps where you get lost/couldn't find.
In the file below, click on the button "Clear Green Cells" to clear the cells mentioned (Which I turned green for easy reference):
https://we.tl/t-UkluTj5EWg
Hopefully the file gives some insight of how it is done.
now I want to delete out the text w/in cell a1. in doing so, I want cells a2-a15 to clear themselves all at once leaving a blank red cell.
can you do that TrowaD? please and thank you.