Related:
- Formula for conditional format to color cells
- Excel conditional formatting 4 color scale formula ✓ - Forum - Excel
- Conditional formatting if another cell contains any text ✓ - Forum - Excel
- Conditional formatting font color ✓ - Forum - Excel
- How To Apply Conditional Formatting on More Cells - How-To - Excel
- Conditional format to highlight cell based on date ✓ - Forum - Excel
4 replies
TrowaD
- Posts
- 2655
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 15, 2020
Hi Kipp,
Give the following formula in conditional format a try:
=AND($A$1<>"",$A2="")
Applied range will be:
=$A$2:$A$15
Best regards,
Trowa
Give the following formula in conditional format a try:
=AND($A$1<>"",$A2="")
Applied range will be:
=$A$2:$A$15
Best regards,
Trowa
TrowaD
- Posts
- 2655
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 15, 2020
Hi Kipp,
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:
Best regards,
Trowa
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
Best regards,
Trowa
kipp2019
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
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.
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.
TrowaD
- Posts
- 2655
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 15, 2020
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 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
kipp2019
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
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.
TrowaD
- Posts
- 2655
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 15, 2020
Hi Kipp,
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:
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.
Best regards,
Trowa
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.
Best regards,
Trowa
kipp2019
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
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.
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.
kipp2019
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
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
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
kipp2019
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
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.
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.
TrowaD
- Posts
- 2655
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 15, 2020
Yep, that is the downside to using macro's.
Just add a message box:
Ctrl + h is used by Excel for Find & Replace, so you might want to reconsider the shortcut you chose.
Just add a message box:
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.
TrowaD
- Posts
- 2655
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 15, 2020
Hi Kipp,
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.
Best regards,
Trowa
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.
Best regards,
Trowa
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.