Formula for conditional format to color cells
Closed
kipp2019
TrowaD
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
TrowaD
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
Related:
- To change conditional formatting that applies a red fill color to one that applies a green fill color, which of the following ca
- You can create conditional cell formats using either the home tab or the - Best answers
- Excel conditional formatting if another cell contains specific text - Best answers
- Conditional formating is applied based on date in two cells ✓ - Forum - Excel
- Conditional formatting change font color based on another cell ✓ - Forum - Excel
- Change color to different cells based on another cells text. ✓ - Forum - Excel
- Excel apply conditional formatting to entire column - Guide
- How to apply same conditional formatting to multiple sheets - Guide
4 replies
TrowaD
May 16, 2019 at 11:40 AM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
May 16, 2019 at 11:40 AM
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
May 20, 2019 at 11:17 AM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
May 20, 2019 at 11:17 AM
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
Updated on May 21, 2019 at 12:32 PM
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
Updated on May 21, 2019 at 12:32 PM
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
May 27, 2019 at 12:20 PM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
May 27, 2019 at 12:20 PM
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
May 28, 2019 at 10:27 AM
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
May 28, 2019 at 10:27 AM
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
Updated on May 28, 2019 at 12:04 PM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
Updated on May 28, 2019 at 12:04 PM
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
Jun 4, 2019 at 11:37 AM
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
Jun 4, 2019 at 11:37 AM
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.
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.
kipp2019
Jun 4, 2019 at 11:54 AM
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
Jun 4, 2019 at 11:54 AM
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
Jun 4, 2019 at 12:28 PM
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
Jun 4, 2019 at 12:28 PM
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
TrowaD
Jun 6, 2019 at 11:34 AM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
Jun 6, 2019 at 11:34 AM
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.
kipp2019
Jun 6, 2019 at 12:04 PM
- Posts
- 8
- Registration date
- Tuesday May 14, 2019
- Status
- Member
- Last seen
- June 6, 2019
Jun 6, 2019 at 12:04 PM
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
Jun 4, 2019 at 12:00 PM
- Posts
- 2884
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 21, 2022
Jun 4, 2019 at 12:00 PM
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
May 16, 2019 at 04:13 PM
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.