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-comfficeffice" /><o></o>
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></o>
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></o>
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></o>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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></o>
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></o>
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></o>