How to auto number cells that have colour
 

Hi

I would like to auto number cells that have only a light yellow colour.
In Row C sheet1 I have numbers starting from 0001 etc, some cells in the
column do not have light yellow these remain blank.
So if a Row C cell becomes light yellow i would like the next number to be
added.

Thanks in Advance

You need to use VBA to do this. Try something like this:-

Select the range from start to finish and then run this code - assumes your
cells are either coloured or not, and that if they are then they get
numbered.


Sub NumberColour()
Dim ActSht As Worksheet
Dim cel As Range
Set ActSht = ActiveSheet

ActSht.Columns("C:C").NumberFormat = "0000"
cntr = 1

For Each cel In Selection
If Not cel.Interior.ColorIndex = xlNone Then
cel.Value = cntr
cntr = cntr + 1
End If
Next cel
End Sub


Regards
Ken....................

"Dave" wrote in message
...
Hi

I would like to auto number cells that have only a light yellow colour.
In Row C sheet1 I have numbers starting from 0001 etc, some cells in the
column do not have light yellow these remain blank.
So if a Row C cell becomes light yellow i would like the next number to be
added.

Thanks in Advance

Thanks Ken



"Ken Wright" wrote in message
...
You need to use VBA to do this. Try something like this:-

Select the range from start to finish and then run this code - assumes
your cells are either coloured or not, and that if they are then they get
numbered.


Sub NumberColour()
Dim ActSht As Worksheet
Dim cel As Range
Set ActSht = ActiveSheet

ActSht.Columns("C:C").NumberFormat = "0000"
cntr = 1

For Each cel In Selection
If Not cel.Interior.ColorIndex = xlNone Then
cel.Value = cntr
cntr = cntr + 1
End If
Next cel
End Sub


Regards
Ken....................

"Dave" wrote in message
...
Hi

I would like to auto number cells that have only a light yellow colour.
In Row C sheet1 I have numbers starting from 0001 etc, some cells in the
column do not have light yellow these remain blank.
So if a Row C cell becomes light yellow i would like the next number to
be added.

Thanks in Advance

you dont say on what condition the cells become coloured.You can use the same
reasoning to number the cells without a macro.If you have selected them
manually with no specific criteria then a macro is your only choice
You need a helper column which can be hidden
you have a column of numbers or letters in A:A
say you want to number only the numbers
in B1=IF(ISNUMBER(A1),1,0)
in b2=IF(ISNUMBER(A2),B1+1,B1) and copy down
Hide columnB:B
in C1=B1
in C2=IF(B2=B1,"",B2)
copy down


paul
remove nospam for email addy!



"Dave" wrote:

Hi

I would like to auto number cells that have only a light yellow colour.
In Row C sheet1 I have numbers starting from 0001 etc, some cells in the
column do not have light yellow these remain blank.
So if a Row C cell becomes light yellow i would like the next number to be
added.

Thanks in Advance