Replace Conditional Formatting with VBA

Femke

New Member
Joined
Apr 9, 2009
Messages
2
Hi, I wonder if anybody could help me with some vba code that can replace the conditional formatting.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
To make the CountColorCF code work properly, I had to make the Conditional formatting formula use absolute cell references instead of relative ones. Since the workbook contains many sheets/cells using conditional formatting this has made the workbook very slow. Therefore I would like to replace the Conditional formatting with code that changes the background colors of the cells instead of coloring them using Conditional formatting. Once I have the Conditional Formatting replaced, I will use the CountColor formula to count the cells that have been highlighted instead of the CountColorCF.
<o:p></o:p>
My workbook consists of blocks of seven columns each, the first block being C4:I33. The Conditional formatting in each of the seven columns looks next to it’s own value at one of the other 6 columns. Only the first column looks at two other columns next to it’s own value.<o:p></o:p>
The 1<SUP>st</SUP> column uses this formula:
=AND(ISNUMBER(D4),ISBLANK(C4),$A4<>1,$A4<>7)

The 2<SUP>nd</SUP>: =AND(ISNUMBER(C4),ISBLANK(D4))
The 3<SUP>rd</SUP>: =AND(ISNUMBER(D4),ISBLANK(E4))
The 4<SUP>th</SUP>: =AND(ISNUMBER(D4),ISBLANK(F4))
The 5<SUP>th</SUP>: =AND(ISNUMBER(F4),ISBLANK(G4))
The 6<SUP>th</SUP>: =AND(ISNUMBER(D4),ISBLANK(H4))
The 7<SUP>th</SUP>: =AND(ISNUMBER(H4),ISBLANK(I4))

I'm sorry for the long explanation.....Hope someone can help! Thanks <o:p></o:p>
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Femke

New Member
Joined
Apr 9, 2009
Messages
2
Just realized that i have not mentioned my version of Excel; it's 2010.

I'm able to use parts of code from other posts but can't make it work...

Any help would be much appreciated!

Regards
femke
 

Forum statistics

Threads
1,144,583
Messages
5,725,132
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top