How to auto number cells that have colour

D

Dave

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
 
K

Ken Wright

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....................
 
D

Dave

Thanks Ken



Ken Wright said:
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....................
Click to expand...
 
P

paul

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!
 
Top