Bright01
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 = True
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
ASKER
Like this?
.Range("B2").ClearFormats
.Range("B2").ClearFormats
Yes
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
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 arFormats
works
works
ASKER
Doesn't work. It clears all of the formatting, not just the color.
B.
B.
Try
.interior.pattern = xlnone
.interior.pattern = xlnone
ASKER
Where do I insert that line?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)?
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