Colorising cells from code

[Isis]

I am using the code below which runs whenever a cell is edited -I think.

I would like to change this code so that it can be 'run' against a range
of cells rather than as Worksheet_Change code.

What do I need to do to do that ?

Thanks

Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Range("A1").Value = "" Then
If Not Intersect(Target, Range("B4:J34, B35:B39")) Is Nothing Then
Select Case Target
Case Sheet3.Range("A4")
icolor = 34
Case Sheet3.Range("A5")
icolor = 35
Case Sheet3.Range("A6")
icolor = 38
Case Sheet3.Range("A7")
icolor = 36
Case Sheet3.Range("A8")
icolor = 37
Case Sheet3.Range("A9")
icolor = 33
Case Else
'icolor = 50
'Whatever
End Select

Application.EnableEvents = False
If icolor > 30 And icolor < 51 Then
Target.Interior.ColorIndex = icolor
End If
Application.EnableEvents = True
End If
End If
End Sub

 

[Bernard Liengme]

Not quite sure what you want.

Sub Tryme()
Dim icolor As Integer
icolor = 34
For Each mycell In Selection
mycell.Interior.ColorIndex = icolor
icolor = icolor + 1
If icolor > 45 Then icolor = 34
Next
End Sub

Select a range of cells and run this one to get different colours
The 45 max is quite arbitrary
best wishes

 

[Isis]

Not quite sure what you want.

Sub Tryme()
Dim icolor As Integer
icolor = 34
For Each mycell In Selection
mycell.Interior.ColorIndex = icolor
icolor = icolor + 1
If icolor > 45 Then icolor = 34
Next
End Sub

Select a range of cells and run this one to get different colours
The 45 max is quite arbitrary
best wishes

Bernard, thanks for taking the time to look at this for me. I did not
make myself clear - I am trying to colour cells based on their contents -
in this instance names.

My names are on another sheet - sheet3.Range(A4:A9)

I want to go through a Range and color the cells based on the name they
have in them (they are all filled from Drop Downs) - so in my original
code I have;
Case Sheet3.Range("A6")
icolor = 38

Can your code be adapted to do that - I am using vba.

Thanks

 

[Bernard Liengme]

What range of cells are to get colours
What range of cells have data
Please explain how to the data determines the colour - give some examples
When you say 'names' I understand you to mean 'text'
best wishes

 

[Isis]

What range of cells are to get colours
What range of cells have data
Please explain how to the data determines the colour - give some
examples When you say 'names' I understand you to mean 'text'
best wishes

Bernard - again, thanks for the reply. Apologies for not being exact in
my definitions.

The sheet is a work timesheet
The 'Names' are peoples names
Part of the sheet contains cells representing work shifts
Each Work Sheet cell has a drop down where you cn pick a staff member
Once picked - the cell gets colored differently for each Name
All same Names have same color

This works well using 'Worksheet_Change' code - but, I also would like to
be able to write out large blocks of names using vba (which I can do) and
then 'zip' through them all coloring them according to my standard name
coloring code afterwards.

So I have an 'automatic' way of coloring cells as they are changed by the
user, but I would also like a 'manual' way that I can use when I need - a
coded (routine) that I can call from wherever.

Sorry for not setting out my actual situation clearly - I really
appreciate the help on offer here,

Thanks