I'm driving myself nuts with this problem. I have a WS that clears itself and the color formats.
It Clears the table but will not clear the color changes in the referenced cells.
I've tried to use the following code and while it works in the "range" to clear and reset, the cells above 42 that are referenced by these table cells, do not change and if I use either the clear or the -0- command, I unfortunately replace the cell reference. Any ideas?
Sub clearHeatmap()
Dim I As Integer
With Worksheets("BAO Heatmap")
.Range("i52:i64").Clear
.Range("i52:i64") = "0"
.Range("d42:d47").Clear
.Range("d42:d47") = "0"
.Range("f42:f47").Clear
.Range("f42:f47") = "0"
.Range("i42:i49").Clear
.Range("i42:i49") = "0"
.Range("k42:k49").Clear
.Range("k42:k49") = "0"
.Range("n42:n48").Clear
.Range("n42:n48") = "0"
.Range("p42:p48").Clear
.Range("p42:p48") = "0"
.Range("s42:s50").Clear
.Range("s42:s50") = "0"
.Range("u42:u50").Clear
.Range("u42:u50") = "0"
.Range("x42:x49").Clear
.Range("x42:x49") = "0"
.Range("A1").Activate
' Application.EnableEvents = True
' Application.ScreenUpdating = True
End With
End Sub
Microsoft Excel
Last Comment
Bright01
8/22/2022 - Mon
Saqib Husain
Try
.clearformats
Bright01
ASKER
Like this?
.Range("B2").ClearFormats
Saqib Husain
Yes
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Bright01
ASKER
I get an X 400 error; whatever that is.
Ideas?
Saqib Husain
Can you upload something?
Bright01
ASKER
Yes.
Here is a blank Model with all of the cells. Check out the reset button and macro attached for clearing the model from the color schemes. Arch-Model.xlsm
Unlimited question asking, solutions, articles and more.
Bright01
ASKER
ssaqibh,
Here's what I think is going on. My original code does reset all of the varibles to -0-. However, the reference (destination) cells do not change back to white/clear......UNTIL, I add again, one varible....then it resets the color coding in the "Heatmap". What I think I need is a line or two of code that when the sheet is "cleared" or reset, it forces an event .........
That's about as far as I can go with it.
B.
Saqib Husain
.Range("B2").mergearea.ClearFormats
works
Bright01
ASKER
Doesn't work. It clears all of the formatting, not just the color.
B.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
This works! Without causing scope creep on this question;
The result has created one additional problem. I have colors that are background colors (i.e. they are not associated with a particular TEXT). The current code requires a text entry that then comes out in the reference cell. One simple solution would be; how do I get a resulting cell reference where the text color is an exact match to the interior color thus producing the right color with no text associated with it (i.e. a background color)?
Unlimited question asking, solutions, articles and more.
Bright01
ASKER
Thank you! Another piece to the ongoing puzzle. I'm going to post a follow up for the following and I hope you will take a stab at it since you understand the code:
I have colors that are background colors (i.e. they are not associated with a particular TEXT). The current code requires a text entry that then comes out in the reference cell. One simple solution would be; how do I get a resulting cell reference where the text color is an exact match to the interior color thus producing the right color with no text associated with it (i.e. a background color)?
.clearformats