set conditional format that sticks when the cell value changes 1

[madelca100]

Hi,

I've seen and used code here that allows me to conditionally set formats for values in a worksheet. (Thanks to all who posted code.)

Currently I use VBA to create a pivot table and change the color of cells (outside of the pivot table) based on its value.

Example: Cell G5, which is not in the pivot table, has this formula: =+e5/f5. If the value is more than 75 the cell background color changes to green; 65-75 yellow; less than 65 red.

After the code executes cell G5 is 74 and yellow. However, if the user changes the layout of the pivot table and the value in G5 changes to 99 the color stays yellow when it should be green.

Using the menu option Format, Conditional Format allows the color to change as the value changes. i.e., the conditional formatting sticks to the cell.

My question: Is there a way to recreate the Format, Conditional Format menu options using VBA? Or is there another way to accomplish this? Because of security concerns I do not want to distribute a worksheet that contains any macros/code.

Any assistance would be greatly appreciated.

mike

 

[SkipVought]

Is there a way to recreate the Format, Conditional Format menu options using VBA?

WHY?

Can this not be done using native CF?

What is your criteris in the CF Wizard with repect to what cell?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[madelca100]

Skip,

Yes it can be done using native CF. That is how we are doing it now.

I would like to automate the process, if possible.

This would avoid errors and would allow the process to run in batch i.e., unattended.

Any ideas?

mike

 

[SkipVought]

And WHERE is the native CF failing?

Also what version Excel are you running?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[madelca100]

Skip,

The CF is not failing.

I want to automate the process to avoid potential human error.

We are using Excel 2003.

mike

 

[SkipVought]

What kind of error do you anticipate?

Please be specific.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

I do not want to distribute a worksheet that contains any macros/code
So, why asking in a VBA forum ?

 

[madelca100]

SV wrote "What kind of error do you anticipate?"

MA Response - human error.
- like miscoding the three conditions.
- picking the wrong shade of red, yellow, green
- things I haven't thought of that another human being might do when they are setting up CF.


PHV wrote "So, why asking in a VBA forum ?"
MA Response - I put code in my Personal.xls so it doesn't reside in the workbook that gets distributed. Do you think this can be done?

 

[SkipVought]

Turn on your macro recorder and record setting the CF criteria as desired.

Post back with your recorded code to get help customizing if required.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[madelca100]

Recorded this macro as a test - which worked.

I copied it down and the conditional formatting sticks.

thanks, skip


Sub CF()

'
' Macro recorded 5/4/2011
'

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="0.95"
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="0.85"
Selection.FormatConditions(2).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="0.85"
Selection.FormatConditions(3).Interior.ColorIndex = 3
End Sub