Conditional Formatting using VBA (Formula)

[eoinymc]

Hi,

I want to use conditional formatting on some cells using the following formula:

=ROUND(SUMIF($D$4:$D$85,"New Profit",E$4:E$85),0)<>ROUND(SUMIF($D$4:$D$85,"Profit",E$4:E$85),0)

When I record the macro, I don't see anything in the Sub..

Has anyone any idea if this can be done?

Thanks,

Eoin

 

[jim may]

Granted your formula (as intended) only results in a TRUE/FALSE. Right? Is that your intention? I do note that you are not using the $ sign BEFORE the "E" reference; Shouldn't you?

I'm not understanding what you are wanting to do.

In code, if you wish to incorporate conditional Formatting the code would include a line, such as (not your example, but):

Range("E18:H23").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF($A$1=""Inactive"",TRUE)"
    Range("E18:H23").FormatConditions(1).Interior.ColorIndex = 48

Please provide more info!!

Jim

 

[eoinymc]

hi Jim,

Basically what I'm trying to do is the following:

I have a lot of products and product groups:
Products A, B, C are in Product Group 1
Products D, E, F are in Product Group 2
Products G, H, I, J are in Product Group 3

So, I have some code that loops through the raw data and puts the information in the format I want it by product and then by product group. The code allows me to do some "what if" analysis to the figures.

At the bottom of each product group, I have a total contribution/profit line. However, if I change any of the figures, I want to have some conditional formatting that shows me if the total of the product groups have changed. And the way I do this is using that formula mentioned previously:

=ROUND(SUMIF($D$4:$D$85,"New Profit",E$4:E$85),0)<>ROUND(SUMIF($D$4:$D$85,"Profit",E$4:E$85),0)

If it is possible to do the above in conditional formatting, I can modify it for the other product groups.

I will be using variables instead of absolute values.. i.e.

=ROUND(SUMIF($D$" & StartRow & $D$" & EndRow"......

I know it's slightly confusing, but any help would be greatly appreciated.

Thanks,

Eoin

 

[jim may]

How does the Range($D$4:$D$85) get populated with either "New Profit" or just "Profit"? Are the "New Profit" ones the ones you are changing the $$$'s on?
I'm not convinced that using Conditional Formatting is the route to take.