Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

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
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Try

.clearformats
Avatar of Bright01

ASKER

Like this?  

  .Range("B2").ClearFormats
I get an X 400 error; whatever that is.

Ideas?
Can you upload something?
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
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.
.Range("B2").mergearea.ClearFormats

works
Doesn't work.  It clears all of the formatting, not just the color.

B.
Try

.interior.pattern = xlnone
Where do I insert that line?
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)?
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)?