asked on Resetting Spreadsheet
EE Professionals,
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
End With
End Sub
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
End With
End Sub
Microsoft Excel
Last Comment
Bright01
ASKER
Like this?
.Range("B2").ClearFormats
.Range("B2").ClearFormats
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
ASKER
I get an X 400 error; whatever that is.
Ideas?
Ideas?
Can you upload something?
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
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
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.
.Range("B2").mergearea.Cle
works
works
ASKER
Doesn't work. It clears all of the formatting, not just the color.
B.
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.
rwheeler23
Try
.interior.pattern = xlnone
.interior.pattern = xlnone
ASKER
Where do I insert that line?
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
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.
ask a questionASKER
Ssaqibh,
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)?
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)?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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)?
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