+ Reply to Thread
Results 1 to 33 of 33

VBA & Conditional Formatting together won't work

  1. #1
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    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

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: VBA & Conditional Formatting together won't work

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

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    Can you make a countdown version of the file and attach that?
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    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

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    Oops, meant cutdown.

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

  6. #6
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    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.

  8. #8
    Registered User
    Join Date
    12-29-2012
    Location
    china,anhui
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: VBA & Conditional Formatting together won't work

    How do I get this conditional formatting to work

  9. #9
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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.

  10. #10
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    Did you try what I suggested?


    The reason I don't think it's a code problem is because I looked through the code and didn't see anything that might affect the formatting in that cell.

    The code for the click event formats C5:E5 and the only event code for that worksheet just shows message boxes.

    PS The video appears to show things working, at one point anyway.
    Last edited by Norie; 12-29-2012 at 05:40 AM.

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    Have you considered not using conditional formatting?
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't 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?

  14. #14
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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?

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    Why is the conditional formatting not working? I think it's because of some existing format.

    When I originally tried I actually cleared all formats in the cell, not just the fill - perhaps you could try that.

    Little confused about the VBA/conditional formatting thing.

    Doesn't it make more sense to add something in the existing code for the checkbox click event that will do the formatting?

    I actually posted code for that in post #12.

    PS I don't have anything to make a video with.

  16. #16
    Registered User
    Join Date
    12-21-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    69

    Re: VBA & Conditional Formatting together won't work

    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.
    Hope this helps.
    Kiran

  17. #17
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    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

  18. #18
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Kiran.Sunkara View Post
    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.

  19. #19
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    Why doesn't using code work?

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

  20. #20
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    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?

  21. #21
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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

  22. #22
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    I posted the code I used.

  23. #23
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    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!

  24. #24
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    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.
    Please Login or Register  to view this content.
    This is the code with the lines I added highlighted in red.
    Please Login or Register  to view this content.
    I've uploaded the file https://www.box.com/s/bi1bm5bme26w2mb4i8f4
    Last edited by Norie; 12-29-2012 at 03:17 PM.

  25. #25
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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.
    Please Login or Register  to view this content.
    This is the code with the lines I added highlighted in red.
    Please Login or Register  to view this content.
    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.

  26. #26
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

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

  27. #27
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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.

  28. #28
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    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.

  29. #29
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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.

  30. #30
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    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.

  31. #31
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    Quote Originally Posted by Norie View Post
    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.

  32. #32
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA & Conditional Formatting together won't work

    I told you how to fix the problem with the 'stained' cell - remove all formatting from the cell, then apply the conditional formatting.

    It worked for me.

  33. #33
    Registered User
    Join Date
    05-16-2008
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 365
    Posts
    41

    Re: VBA & Conditional Formatting together won't work

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1