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

Edited the question so people may be able to answer.
Thanks a lot anyone

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?

"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.

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.

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.

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?

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?

Test this code on a copy of your workbook.

Please Login or Register  to view this content.

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

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.

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.

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.