Formula for conditional format to color cells

Posts
8
Registration date
Tuesday May 14, 2019
Status
Member
Last seen
June 6, 2019
-
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.
See more 

4 replies

Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370
0
Thank you
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
kipp2019
Posts
8
Registration date
Tuesday May 14, 2019
Status
Member
Last seen
June 6, 2019
-
amazing. absolutely unbelievably amazing. great job. spot on 100% correct. thank you SO much. now, the reward for good work is more work.

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.
Respond to TrowaD
Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370
0
Thank you
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:
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.
TrowaD
Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370 -
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
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.
Respond to TrowaD
Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370
0
Thank you
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:
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.
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
TrowaD
Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370 -
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
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.
TrowaD
Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370 -
Yep, that is the downside to using macro's.

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.
Respond to TrowaD
Posts
2562
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 14, 2019
370
0
Thank you
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
Respond to TrowaD