VBA & Conditional Formatting together won't work

I'm working with a spreadsheet that I got elsewhere and tried to add conditional formatting to it. However, I can't.

The excel file was to large to upload so I took screenshots. The checkbox in cell E3 links to cell E8. Cell E9 was conditionally formatted to turn red when cell E8 = TRUE.

the first screenshot ("before") is before any formatting is initiated.

the second screenshot ("after") is when the checkbox is clicked and the value in E8 = TRUE. However, in cell E9, it appears as though the grey that was there before is like an image that is "in front" of the cell's color. You can see the red peaking through a little bit.

the third screenshot ("after highlight") is what happens if I highlight the area.... the red then shows through.

What the heck is happening here? How do I get this conditional formatting to work?

before.jpg

after.jpg

after highlight.jpg

could you post the macro that is handling the conditional formatting?

Hi, I don't know which macro is handling the conditional formatting. I entered the conditional formatting via the conditional formatting button under the "HOME" tab.

There are 10 macros in the book, one that is 5500+ lines. The others don't really relate to it. There is a pop up message creator macro in sheet1, but even removing those doesn't solve the issue.

How do I make a "countdown version" of the file? Maybe I can post the full file somewhere online for download....

Maybe this will work: Here's the download (without the conditional formatting in it)... you'll have to add it in to re-create.

http://www.actuarialoutpost.com/actu...3&d=1353446461

Originally Posted by Norie

Oops, meant cutdown.

Why don't you include the conditional formatting in the workbook you posted a link for?

Because I can't. The file is too big to post here.

The changes are pretty simple:

The checkbox in cell E3 links to cell E8.
Cell E9 was conditionally formatted to turn red when cell E8 = TRUE.

Originally Posted by Norie

I don't think the problem has anything to do with code.

If I set E9 to have no fill after applying the conditional formatting everything seems fine.

How?!? It certainly doesn't work for me. The cell doesn't change besides a little section at the top (i.e. like the top border only). I will try to make a video and post it.

Originally Posted by Norie

I don't think the problem has anything to do with code.

If I set E9 to have no fill after applying the conditional formatting everything seems fine.

I have no clue how you got it to work... please share! Here's a link to the video of what happens:

http://tinypic.com/player.php?v=10ehhtu&s=6

As you can see, no fill. Conditional formatting... doesn't work. It's like the cell gets "stained" with the previous color.

Originally Posted by Norie

I don't think the problem has anything to do with code.

If I set E9 to have no fill after applying the conditional formatting everything seems fine.

Can you post a video of it working?

How do I get this conditional formatting to work

Yes, I did try (i.e. putting "no fill" into the cell). The video actually has no fill. I didn't see anywhere in the video of the conditional formatting working. The color only changed when I highlight the cell.

As for the conditional formatting written into the VBA... yes, that is what I am trying to do. I can remove all that and replace it with regular conditional formatting not tied to VBA.

However, the problem still remains that the cells do not change colors with the regular conditional formatting. It does work in cells not in the body of the spreadsheet (i.e. some cell off the main part of the sheet). So the cells are formatted or something, that prevents them from changing colors. What that is, I have no clue. What could be doing that?

How did you "clear all formatting in the cell"? I believe i did this, but maybe I didn't. I did this:
http://tipsforspreadsheets.com/micro...ormatting.html

And no, that doesn't work for me. Same thing as in the video.

The issue is that I want to protect to the sheet so that ONLY unlocked cells can be selected. Nothing else. If I protect the sheet that way, it doesn't work. I need to allow "format cells" to also be selected because the VBA code requires it. That is the reason why it does not make sense to work with the existing VBA code. The VBA code is what I'm trying to eliminate because it restricts protections.

But you have figured out a way for it to work so please help me. I've tried what you said and it does not work (as per my video).

Its quite simple to make the video:

Use this to record your video. Its a great program. Just lower the video resolution to keep the file size smaller:
http://camstudio.org/

If you have Windows 7, you'll need to download this update:
http://www.microsoft.com/en-us/downl...s.aspx?id=5582

Originally Posted by Kiran.Sunkara

Agree with Nories, everything seem to be working normal and as per your requirement.
You don't need any conditional formatting, couple of lines of code should do the magic.
As suggested by Nories, please replace the above code under Sub hsa_Click()
Range("E9").Interior.ColorIndex is the addtion in the code.

Did you see my video? I certainly don't call that "working normal".... Here is the video:
http://tinypic.com/player.php?v=10ehhtu&s=6

You'll notice the colors do not change unless I highlight the cell.

Working with the code will not help because it doesn't solve the problem: i.e. must allow "format cells" under protect the sheet option.

The issue is that I want to protect to the sheet so that ONLY unlocked cells can be selected. Nothing else. If I protect the sheet that way, it doesn't work. I need to allow "format cells" to also be selected because the VBA code requires it. That is the reason why it does not make sense to work with the existing VBA code. The VBA code is what I'm trying to eliminate because it restricts protections.

Let's get on the same page. Take the spreadsheet... select protect worksheet and only allow unlocked cells to be selected. Try to run the macros. What happens?

Originally Posted by Norie

Why doesn't using code work?

You are already using it to format other cells, so what difference does it make to format another?

Please post a video of it working for you if you can. I want to see what you did. Camstudio is a great program

I'm sorry Norie, I don't mean to be rude. But you are completely missing the point. The point here is NOT to highlight cells C5:E5. The point is what I have said 2 times now:

Please read post #17 & #20. How does your code address that issue? As far as I can tell, it doesn't. Before you had said you found a way to solve the issue that I originally posted (i.e. the "staining of the cells" shown in the jpg's and the video that I posted)... please share!

Originally Posted by Norie

Where have I mentioned fornatting C5:E5 apart from to say that's what's your code does.

I've adde 2 lines of code to format C9 based on whether the checkbox is clicked or not.

This is the original code.

Sub hsa_Click()

    If Sheet1.CheckBoxes("hsa").Value = 1 Then
        Range("C5:E5").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        Range("C5:E5").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.499984740745262
            .PatternTintAndShade = 0
        End With
    End If
End Sub

This is the code with the lines I added highlighted in red.

Sub hsa_Click()

    If Sheet1.CheckBoxes("hsa").Value = 1 Then
        Range("C5:E5").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("E9").Interior.ColorIndex = 3
    Else
        Range("C5:E5").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.499984740745262
            .PatternTintAndShade = 0
        End With
        Range("E9").Interior.ColorIndex = xlNone
    End If
End Sub

I've uploaded the file https://www.box.com/s/bi1bm5bme26w2mb4i8f4

Again, I don't mean to be rude. But the stuff you are saying has nothing to do with what I am trying to do. Please read posts #17 & #20. Your code does NOT address that situation. Go ahead, try it! Protect the workbook. Then close the file. Then re-open and try to run the macros.

You keep missing the point. It is NOT about using VBA for conditional formatting. I don't care what VBA you use.. you will not be able to solve the problem I am trying to solve with VBA. PLEASE READ POSTS #17 & #20. I will post again:

The issue is that I want to protect to the sheet so that ONLY unlocked cells can be selected. Nothing else. If I protect the sheet that way, it doesn't work. I need to allow "format cells" to also be selected because the VBA code requires it. That is the reason why it does not make sense to work with the existing VBA code. The VBA code is what I'm trying to eliminate because it restricts protections.

Originally Posted by Norie

So this is to do with protection and running the macros then?

Yes. Please read. Do this and you will instantly get it:

Protect the workbook. Then close the file. Then re-open and try to run the macros.

Do it please, you've probably spent a lot of time answering a question that I never asked. At least by doing this, you will understand my question.

Originally Posted by Norie

Running the macros with the workbook protected has never been mentioned, until recently.

As far as I can see the problem when the worksheet is protected is the checkbox's linked cell.

Pardon, worksheet. I said worksheet many many times now. I appreciate your effort, but please read. Your effort has produced very little help. In post #7... you said you had it fixed. I still don't see how you had it fixed. If you have a solution based on what you said there, please explain / post a link. In post #11, you say that in the video I posted, the formatting works. I have no clue how you see that... it certainly does not. The original problem of the cell being "stained" with the previous color remains. Then you posted your VBA based solution in post #12... without clarifying what the heck you did in post #7. Then you continued to push the VBA code idea... to which I explained was inadequate in post #17.

I'm sorry, but I think a problem here is that you don't read / listen to the problem.

No, your assessment is not correct that the main problem is with the checkbox's linked cell. Go ahead and remove the checkbox's linked cell. It makes no difference. Did you do what I asked?

Protect the worksheet and ONLY allow unlocked cells to be selected. NOTHING ELSE. Then close the file. Then re-open and try to run the macros.

The macros cannot run.

THIS IS THE PROBLEM. Not anything you have previously mentioned. I have the solution. Just use regular conditional formatting that is available on the "home" ribbon tab. However, this leads to another problem... i.e. the cell being "stained" with the previous color.

Originally Posted by Norie

Well, I'm glad you found a solution.

Perhaps if you had made the problem a little cleared right from the start it might have helped.

No I did not find a solution. You again did not read what I wrote. "However, this leads to another problem... etc."

I was very clear about the problem. i.e. the cell being "stained" with the previous color when conditional formatting is used.

Thanks for your effort, although it was not very helpful and quite frustrating.

Here's the video of me doing exactly what you recommended. I'm sorry Norie, it does NOT work. The cell remains "stained" until I highlight the cell with my mouse.

http://www.flickr.com/photos/4332691...in/photostream

If it works for you, please post a video of what you did. It is extremely easy. I already told you this method doesn't work.. way back in post #9.