Avatar of Bright01
Bright01
Flag 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
Microsoft Excel

Avatar of undefined
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
Get an unlimited membership to EE for less than $4 a week.
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.
rwheeler23
Saqib Husain

Try

.interior.pattern = xlnone
Bright01

ASKER
Where do I insert that line?
ASKER CERTIFIED SOLUTION
Saqib Husain

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
Sign up - Free for 7 days
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 question
Bright01

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)?
Get an unlimited membership to EE for less than $4 a week.
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)?