+ Reply to Thread
Results 1 to 21 of 21

Conditional Formatting using cell values

  1. #1
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Unhappy Conditional Formatting using cell values

    Hey everyone,

    I want to use VBA to apply conditional formatting that can be updated later on very simply.
    Because I am still new to VBA, I am not good with properties or defining variables and I'm sure that I am doing this wrong. I want to apply/update the five conditional formats for three different sheets, so I attempted some for loops. Even without the for loops, i don't know if what I am doing is possible. Any help would be appreciated!

    Screen Shot 2015-05-11 at 3.15.28 PM.png
    Please Login or Register  to view this content.
    8Resource_Test5-9-15.xlsm
    Here is the sheet ! Thanks for taking a look!
    Red
    Last edited by RedSummer; 05-11-2015 at 07:35 PM.

  2. #2
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Complicated Conditional Formatting Based Off Formula and Range Written Within the Shee

    Bumping, no response*

  3. #3
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Complicated Conditional Formatting Based Off Formula and Range Written Within the Shee

    Edited the question so people may be able to answer.
    Thanks a lot anyone
    Last edited by RedSummer; 05-11-2015 at 03:28 PM.

  4. #4
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    Bout to reach the third page so Bumping again

  5. #5
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    Anyone think they can figure this out?

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    Can you answer these questions?
    1) So you want to programmatically change conditional formatting?
    2) The rules you want to apply are in A10:A14?
    3) The ranges you want to apply them to are in C10:C14?
    4) The actual RGB colors are in F10:F14? These are background colors?
    5) All of the above changes when you change B4 or C4?
    6) Which sheets do you want to apply them to?
    Last edited by skywriter; 05-13-2015 at 08:13 PM.
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  7. #7
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    Quote Originally Posted by skywriter View Post
    Can you answer these questions?
    1)So you want to programmatically change conditional formatting?
    Yes, the intention is that this will make it easier for non-experienced excel users to keep the formatting consistent. Even if cut,copy, and pasting is occurring on the sheets, a simple press from this button will return the chaos to consistent formatting (see question #5).

    Apologies, it looks like the sheet uploaded did not correspond with the image I attached.
    Where the range and rules have switched places and I attempted to create cells with value for the formatting already.

    9Resource_Test5-9-15.xlsm


    Quote Originally Posted by skywriter View Post
    2) The rules you want to apply are in A10:A14?
    Yes they are in the old document, now they are B10:B14 (well they are merged cells so I wonder if that causes an issue)

    Quote Originally Posted by skywriter View Post
    3) The ranges you want to apply them to are in C10:C14?
    Yes they were, although in the document just uploaded they are A10:A14.

    Quote Originally Posted by skywriter View Post
    4) The actual RGB colors are in F10:F14? These are background colors?
    Yes, I have determined what the RGB colors are, they are background colors. In the new document I have moved this to column I.

    Quote Originally Posted by skywriter View Post
    5) All of the above changes when you change B4 or C4?
    B4 and C4 affect the "applies to" range, allowing people who are using the sheet to refine the limits of the conditional formatting should they fill out enough rows or columns that the formatting is no longer automatic. So they wouldn't make the background a different color, for example.

    Quote Originally Posted by skywriter View Post
    6) Which sheets do you want to apply them to?
    "Resources by Commodity" , "Resources by Company", "Resources by Country"

    Thanks for taking a look! I sincerely hope I am not confusing you.

  8. #8
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    "Resources by Commodity" , "Resources by Company", "Resources by Country"

    Thanks for taking a look! I sincerely hope I am not confusing you.
    No you are not confusing me. I have been subscribed to your thread for a while. Mainly because I have no experience coding with conditional formatting and I was hoping some of the many real experts on here were going to step up and help you.

    I see you keep bumping your thread and no one is responding, so here I am, I'll give it a shot.

    Don't let the lack of coding conditional formatting worry you though. I am very resourceful and my coding knowledge is getting to the point where I can read someone's code and it doesn't take me long to understand it. I have a pretty good understanding of how to make conditional formatting do what I want through the normal user interface, so I have confidence I can help you get where you want to be. However be patient with my questions and the more I can get out of you the easier it will be.

    So tell me about the code you have written, does it do anything you want, are there just a few snags. You commented early on about not being good at properties and variables, I can probably help you there. If you can tell me about the code where the snags are etc. While awaiting your answer I will do a little research, experimentation etc. to get a feel for how to code conditional formatting.

  9. #9
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    I appreciate your help!

    So the code I posted does not work in the slightest.
    I was unsure of the method needed to run a For Each that would allow my For loop to work for each sheet (not every sheet).

    Let me make this clearer. The document has 8 sheets. Some are for instructions, some are notes, but the three sheets that need conditional formatting are:
    Resources by Commodity, Resource by Company, Resource by Country

    I need to take values entered into cells on my formatting sheet, and use those values as inputs to answer FormatConditions concerning a range for another sheet.
    I'm realizing more and more I have no idea how to do this.

    I'm really starting to confuse myself.
    Please Login or Register  to view this content.

  10. #10
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    So far I'm able to add a rule to whatever sheet I want and have the applies to range work the way I want. The problem is using the rule in a variable. For instance the rule you have in cell B10 is C$2<>"", I can get this into a variable no problem but in conditional formatting it ends up as ="C$2<>""""", I know why it does this but I'm not sure of how to get rid of the extra "" when using a variable.
    Anyway here's the code, it's not much but if I can figure out the formula part it will probably get easier from there.

    Please Login or Register  to view this content.

  11. #11
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    This will loop through the sheets and apply the formatting to the cells specified in A10 with the rule specified in B10. I will continue working on the code for the other rules.

    You should just use a copy of your sheet for this because it's going to wipe out all the conditional formatting before only applying the first rule.

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    Some serious stuff in that code that is hard for me to follow XD

    I'm starting to doubt if what we're trying to do is possible now

  13. #13
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    I'm starting to doubt if what we're trying to do is possible now
    Why? You didn't give me any feedback on this. What's the issue?

  14. #14
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    I spent some time really reading through your code and it's really quite genius. Now we need another for loop and a bunch more variable names for the format conditions right?
    I'm going to mess with it
    Sorry for the moment of doubt!
    Wish there was a way for VBA to read a preset formatted condition :/


    Instead of
    Please Login or Register  to view this content.
    Can we set the strAppliesTo and the strFormula to be an Array (.range("A10").Value, .range("A11").value.....
    and so forth?
    Then call the for loop of 0 to 4 inside the worksheet for loop?
    Last edited by RedSummer; 05-13-2015 at 12:09 PM.

  15. #15
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    Test this code on a copy of your workbook.

    Please Login or Register  to view this content.
    Last edited by skywriter; 05-13-2015 at 04:37 PM.

  16. #16
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    This is absolutely incredible. Does it how I would have wanted it, I love you for figuring this out.

    I'm experiencing one bug where one of the rules is being hidden by the other so I swapped them, and also added a .StopIfTrue = False
    but now one color is still not showing up.
    I'll post back in a second with some pics.

  17. #17
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    Okay it's almost perfect. Absolutely beautiful that you have given me that capability I am so thankful.

    I'm not sure as to why I can't get that single last formula to show though, it also does not look at the stopiftrue = False that all conditional formats should have by virtue of being in the same loop.

    And also, an update, I only want that first condition to have white font.
    The others I would like to find a way to keep font cleared from the formatting. That way hyperlinks on those pages remain blue. Do you think this is possible? Back when I did it by hand, I would create the rule, custom format, and click "clear" where the font was. This left a "no format set" upon which I'd just add a fill.

    the "no format set" is what I am seeing where there should be the format for the last rule.

    Any thoughts?

    10Resource_Test5-13-15.xlsm
    Last edited by RedSummer; 05-13-2015 at 05:44 PM.

  18. #18
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    I'm not sure what you are talking about.
    The only rule I see that has the stop if true checked in the last one and I don't see where that would matter, because it is the last rule.
    The formulas are all there and the formats look just like they do in the examples.
    Give me specifics on what sheet you are looking at etc.
    Remember when you go to conditional formatting you need to choose the selection this worksheet to see all the rules in a particular sheet. I think the default is to see the rules that apply to the cell that is currently selected.

  19. #19
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    I see what you are talking about. I figured the problem was because we were adding more than one rule for the same range, but I couldn't find anything helpful on the internet. I played around with the code a little bit and discovered a solution. I told you I was resourceful. Sometimes my resourcefulness is just my willingness to experiment.

    If you are happy with my help, please consider clicking the add reputation button in the lower left hand corner of this post.


    Please Login or Register  to view this content.
    Last edited by skywriter; 05-13-2015 at 07:41 PM.

  20. #20
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Conditional Formatting using cell values

    Just amazing man, absolutely amazing.
    After testing with them on and off, I don't know if the borders actually work, but it doesn't matter anymore to me.
    I also got the font color to only appear for the header by using an if statement associated with y 10. This is not very dynamic but I figured people won't be moving rules and ranges around or anything. Here's my addition:
    Please Login or Register  to view this content.

  21. #21
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Conditional Formatting using cell values

    Thanks for the rep. points. I learned a lot from this, thanks for that.

    If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Complicated Conditional Formatting based on 2 Tables
    By mtma in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-07-2014, 03:59 PM
  2. Conditional Formatting via VBA: Change formatting in range based on value of each cell
    By ralphjmedia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 10:37 AM
  3. 3+ Conditional Formatting Rules for cell range based on Formula from other cells
    By osborsm9 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2013, 08:48 PM
  4. Conditional Formatting - Grey Out and Lock A range based on a cell value in that range
    By Excelgnome in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2012, 07:31 PM
  5. Replies: 7
    Last Post: 07-30-2008, 12:45 PM

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