I would be very glad if anybody in this forum can help me with one of my
unknown.

from a large spreadsheet, filled with data and formulas, no outside
worksheet linking, I like to have the cells to have conditionally filled
colors,

e.g (in one sheet).
if one cell is use in a formula by other cellS, i need it to have an
automatic color

the gradient of colors can be chosen as you can suggest, and must depend on
the following intermittent range of condition
1. if a cell is referred by a formula by about -say maximum- 1000 times
then the color should be gold,
2. when the cell is the least referred by a formula by about - say minimum -
1 time then the color should be light green.
3. for cells used on intermittent times, u can suggest colors.

maybe this is possible to help me clean up many large muddy files - with
irrelevant data.

thanks for any suggestion.

I am sorry that this is not the complete solution, only a start:

Select an area of cell in the worksheet and run:

Sub color_me_yellow()
test = ""
For Each r In Selection
On Error Resume Next
If r.Dependents.Address = "" Then
Else
r.Interior.ColorIndex = 6
End If
Next
End Sub

If a data cell is being used by another cell's formula, it will be colored
yellow. At least you can tell is a data cell is not really being used.
--
Gary's Student


"dribler2" wrote:

I would be very glad if anybody in this forum can help me with one of my
unknown.

from a large spreadsheet, filled with data and formulas, no outside
worksheet linking, I like to have the cells to have conditionally filled
colors,

e.g (in one sheet).
if one cell is use in a formula by other cellS, i need it to have an
automatic color

the gradient of colors can be chosen as you can suggest, and must depend on
the following intermittent range of condition
1. if a cell is referred by a formula by about -say maximum- 1000 times
then the color should be gold,
2. when the cell is the least referred by a formula by about - say minimum -
1 time then the color should be light green.
3. for cells used on intermittent times, u can suggest colors.

maybe this is possible to help me clean up many large muddy files - with
irrelevant data.

thanks for any suggestion.

I have belief on you Gary, that's a good start. surely you can extend this
macro, let's say a color "bright green" for cells with a formula...please
proceed.
happy holidays
4pinoy

"Gary''s Student" wrote:

I am sorry that this is not the complete solution, only a start:

Select an area of cell in the worksheet and run:

Sub color_me_yellow()
test = ""
For Each r In Selection
On Error Resume Next
If r.Dependents.Address = "" Then
Else
r.Interior.ColorIndex = 6
End If
Next
End Sub

If a data cell is being used by another cell's formula, it will be colored
yellow. At least you can tell is a data cell is not really being used.
--
Gary's Student


"dribler2" wrote:

I would be very glad if anybody in this forum can help me with one of my
unknown.

from a large spreadsheet, filled with data and formulas, no outside
worksheet linking, I like to have the cells to have conditionally filled
colors,

e.g (in one sheet).
if one cell is use in a formula by other cellS, i need it to have an
automatic color

the gradient of colors can be chosen as you can suggest, and must depend on
the following intermittent range of condition
1. if a cell is referred by a formula by about -say maximum- 1000 times
then the color should be gold,
2. when the cell is the least referred by a formula by about - say minimum -
1 time then the color should be light green.
3. for cells used on intermittent times, u can suggest colors.

maybe this is possible to help me clean up many large muddy files - with
irrelevant data.

thanks for any suggestion.

Sub color_me_yellow_or_green()
test = ""
For Each r In Selection
On Error Resume Next
If r.Dependents.Address = "" Then
Else
r.Interior.ColorIndex = 6
End If

If r.HasFormula Then
r.Interior.ColorIndex = 4
End If
Next
End Sub

Now the used data cells are yellow and cells with formulas are bright green.

I will post something much better one day from today.
--
Gary''s Student


"dribler2" wrote:

I have belief on you Gary, that's a good start. surely you can extend this
macro, let's say a color "bright green" for cells with a formula...please
proceed.
happy holidays
4pinoy

"Gary''s Student" wrote:

I am sorry that this is not the complete solution, only a start:

Select an area of cell in the worksheet and run:

Sub color_me_yellow()
test = ""
For Each r In Selection
On Error Resume Next
If r.Dependents.Address = "" Then
Else
r.Interior.ColorIndex = 6
End If
Next
End Sub

If a data cell is being used by another cell's formula, it will be colored
yellow. At least you can tell is a data cell is not really being used.
--
Gary's Student


"dribler2" wrote:

I would be very glad if anybody in this forum can help me with one of my
unknown.

from a large spreadsheet, filled with data and formulas, no outside
worksheet linking, I like to have the cells to have conditionally filled
colors,

e.g (in one sheet).
if one cell is use in a formula by other cellS, i need it to have an
automatic color

the gradient of colors can be chosen as you can suggest, and must depend on
the following intermittent range of condition
1. if a cell is referred by a formula by about -say maximum- 1000 times
then the color should be gold,
2. when the cell is the least referred by a formula by about - say minimum -
1 time then the color should be light green.
3. for cells used on intermittent times, u can suggest colors.

maybe this is possible to help me clean up many large muddy files - with
irrelevant data.

thanks for any suggestion.

You are a very helpful person, it really works for two type of cells.
I will really wait for your finale reply of this question. Excel fanatics
will really be amazed how you can instantly render colors on cells with great
importance to the user. Please don't forget about the gradient effects from
GOLD to Bright green...This excel macro will show the importance of a single
cell to a worksheet.

i am ready to receive your instruction whether to build on a separate sheet
for a database of a user's taste of color gradient versus the user's choice
of cell's number of times of usage in a formulated worksheet.

I know that you know the purpose of this for all formula mania users. Making
excel users to live in a colorful life with importance.

happy holidays hohoho
romelsb:)

"Gary''s Student" wrote:

Sub color_me_yellow_or_green()
test = ""
For Each r In Selection
On Error Resume Next
If r.Dependents.Address = "" Then
Else
r.Interior.ColorIndex = 6
End If

If r.HasFormula Then
r.Interior.ColorIndex = 4
End If
Next
End Sub

Now the used data cells are yellow and cells with formulas are bright green.

I will post something much better one day from today.
--
Gary''s Student


"dribler2" wrote:

I have belief on you Gary, that's a good start. surely you can extend this
macro, let's say a color "bright green" for cells with a formula...please
proceed.
happy holidays
4pinoy

"Gary''s Student" wrote:

I am sorry that this is not the complete solution, only a start:

Select an area of cell in the worksheet and run:

Sub color_me_yellow()
test = ""
For Each r In Selection
On Error Resume Next
If r.Dependents.Address = "" Then
Else
r.Interior.ColorIndex = 6
End If
Next
End Sub

If a data cell is being used by another cell's formula, it will be colored
yellow. At least you can tell is a data cell is not really being used.
--
Gary's Student


"dribler2" wrote:

I would be very glad if anybody in this forum can help me with one of my
unknown.

from a large spreadsheet, filled with data and formulas, no outside
worksheet linking, I like to have the cells to have conditionally filled
colors,

e.g (in one sheet).
if one cell is use in a formula by other cellS, i need it to have an
automatic color

the gradient of colors can be chosen as you can suggest, and must depend on
the following intermittent range of condition
1. if a cell is referred by a formula by about -say maximum- 1000 times
then the color should be gold,
2. when the cell is the least referred by a formula by about - say minimum -
1 time then the color should be light green.
3. for cells used on intermittent times, u can suggest colors.

maybe this is possible to help me clean up many large muddy files - with
irrelevant data.

thanks for any suggestion.

Well here it is:


Sub color_me_yellow_or_green()

Dim yellow As Integer ' unpopular
Dim bright_green As Integer ' popular
Dim gold As Integer ' very popular
Dim pink As Integer ' super popular
Dim light_blue As Integer ' a function

yellow = 6
bright_green = 4
gold = 44
light_blue = 33
pink = 7

unpopular = 10
popular = 50
very_popular = 100


For Each r In Selection
c = 0
On Error Resume Next
c = r.Dependents.Count
Select Case c
Case 0 To unpopular
r.Interior.ColorIndex = yellow
Case unpopular + 1 To popular
r.Interior.ColorIndex = bright_green
Case popular + 1 To very_popular
r.Interior.ColorIndex = gold
Case Is very_popular
r.Interior.ColorIndex = pink
End Select
Next

For Each r In Selection
If r.HasFormula Then
r.Interior.ColorIndex = light_blue
End If
Next

End Sub



The color coding is

light blue for functions
yellow for data used in 10 or less places
bright green for data used in 11 to 50 places
gold for data used in51 to 100 places
pink for data used in more than 100 plces

--
Gary''s Student


"dribler2" wrote:

You are a very helpful person, it really works for two type of cells.
I will really wait for your finale reply of this question. Excel fanatics
will really be amazed how you can instantly render colors on cells with great
importance to the user. Please don't forget about the gradient effects from
GOLD to Bright green...This excel macro will show the importance of a single
cell to a worksheet.

i am ready to receive your instruction whether to build on a separate sheet
for a database of a user's taste of color gradient versus the user's choice
of cell's number of times of usage in a formulated worksheet.

I know that you know the purpose of this for all formula mania users. Making
excel users to live in a colorful life with importance.

happy holidays hohoho
romelsb:)

"Gary''s Student" wrote:

Sub color_me_yellow_or_green()
test = ""
For Each r In Selection
On Error Resume Next
If r.Dependents.Address = "" Then
Else
r.Interior.ColorIndex = 6
End If

If r.HasFormula Then
r.Interior.ColorIndex = 4
End If
Next
End Sub

Now the used data cells are yellow and cells with formulas are bright green.

I will post something much better one day from today.
--
Gary''s Student


"dribler2" wrote:

I have belief on you Gary, that's a good start. surely you can extend this
macro, let's say a color "bright green" for cells with a formula...please
proceed.
happy holidays
4pinoy

"Gary''s Student" wrote:

I am sorry that this is not the complete solution, only a start:

Select an area of cell in the worksheet and run:

Sub color_me_yellow()
test = ""
For Each r In Selection
On Error Resume Next
If r.Dependents.Address = "" Then
Else
r.Interior.ColorIndex = 6
End If
Next
End Sub

If a data cell is being used by another cell's formula, it will be colored
yellow. At least you can tell is a data cell is not really being used.
--
Gary's Student


"dribler2" wrote:

I would be very glad if anybody in this forum can help me with one of my
unknown.

from a large spreadsheet, filled with data and formulas, no outside
worksheet linking, I like to have the cells to have conditionally filled
colors,

e.g (in one sheet).
if one cell is use in a formula by other cellS, i need it to have an
automatic color

the gradient of colors can be chosen as you can suggest, and must depend on
the following intermittent range of condition
1. if a cell is referred by a formula by about -say maximum- 1000 times
then the color should be gold,
2. when the cell is the least referred by a formula by about - say minimum -
1 time then the color should be light green.
3. for cells used on intermittent times, u can suggest colors.

maybe this is possible to help me clean up many large muddy files - with
irrelevant data.

thanks for any suggestion.