Color Coding with VBA

[Rasec]

Hi All,

I have a 40 tab worksheet that is a convoluted mess. Hardcoded numbers mixed with formulas, hardcoded numbers is random cells, cells referencing other tabs and being affected by hardcoded number and formulas.

I was able to color in green all 20,000+ cells which have a link to other cells by searching for "!"

Now I have three questions:
1 - How could I find only hardcoded cells and color them in blue with a yellow background?

2 - How can I color a cell which has a hardcoded number AND a formula (ONLY!!). This would exclude cells with Hardcoded numbers + formulas + links to other cells (!)

3 - I plan to leave cells which contain only formulas blank

Please see example below:

<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=199><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl269 height=17 width=64 align=right>20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl269 width=64 align=right>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl268 width=71 align=right>50,472,420</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl270 height=17 align=right>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 325pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=432><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 1280" width=70><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 731" width=40><COL style="WIDTH: 242pt; mso-width-source: userset; mso-width-alt: 5888" width=322><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl269 height=17 width=70>20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; WIDTH: 30pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl269 width=40>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 242pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl268 width=322>='Nedbank Senior'!C329-'Commitment Fee - AfDB'!B8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>=B4-C4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl270 height=17>=(B4-B5)/2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>

 

[mikerickson]

1) Use Edit>Goto>Constants. That will select cells with only values (no formulas, no blanks). They can then be formatted en masse.

2) What would be an example of "a cell which has a hardcoded number AND a formula (ONLY!!)"?

3) You can use Goto to select cells with formula and then delete them. You will be left with empty cells.
Or by "blank" do you mean that the cell displays nothing, even though the cell has a formula. If that is the case, how are you going to see the result of the formula?

 

[Rasec]

1) Use Edit>Goto>Constants. That will select cells with only values (no formulas, no blanks). They can then be formatted en masse.

2) What would be an example of "a cell which has a hardcoded number AND a formula (ONLY!!)"?

3) You can use Goto to select cells with formula and then delete them. You will be left with empty cells.
Or by "blank" do you mean that the cell displays nothing, even though the cell has a formula. If that is the case, how are you going to see the result of the formula?

2) you see the yellow background with red font? that is a formula and a hardcoded number. for e.i., =D19-E13*100000

3) If I see this =(D3^E5) - (T6/T1) * D23)), I leave it with a white background and black font!!! You see this has no hardcoded numbers!!!