Is it possible to enter a color number (1-56 for example) and make that cell
the color in question?

I am guessing conditional formatting might be a way, but i think some code
may be needed.

Can anyone help?

Thanks,

Roger

Right click sheet tabview codecopy/paste this. You may want to restrict
the cells involved.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in message
...
Is it possible to enter a color number (1-56 for example) and make that
cell
the color in question?

I am guessing conditional formatting might be a way, but i think some code
may be needed.

Can anyone help?

Thanks,

Roger

Hi Don,

Nice - it works well.

by the way, how would i restrict this to cell "b1"?

Thanks,

Roger



"Don Guillett" wrote:

Right click sheet tabview codecopy/paste this. You may want to restrict
the cells involved.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in message
...
Is it possible to enter a color number (1-56 for example) and make that
cell
the color in question?

I am guessing conditional formatting might be a way, but i think some code
may be needed.

Can anyone help?

Thanks,

Roger

Private Sub Worksheet_Change(ByVal Target As Range)

if target.address<"$B$1" then exit sub
'don't forget $ or " " and use CAPS for cell address

On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in message
...
Hi Don,

Nice - it works well.

by the way, how would i restrict this to cell "b1"?

Thanks,

Roger



"Don Guillett" wrote:

Right click sheet tabview codecopy/paste this. You may want to restrict
the cells involved.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in
message
...
Is it possible to enter a color number (1-56 for example) and make that
cell
the color in question?

I am guessing conditional formatting might be a way, but i think some
code
may be needed.

Can anyone help?

Thanks,

Roger

Excellent Don.

Thanks very much for this

Best regards, Roger



"Don Guillett" wrote:


Private Sub Worksheet_Change(ByVal Target As Range)

if target.address<"$B$1" then exit sub
'don't forget $ or " " and use CAPS for cell address

On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in message
...
Hi Don,

Nice - it works well.

by the way, how would i restrict this to cell "b1"?

Thanks,

Roger



"Don Guillett" wrote:

Right click sheet tabview codecopy/paste this. You may want to restrict
the cells involved.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in
message
...
Is it possible to enter a color number (1-56 for example) and make that
cell
the color in question?

I am guessing conditional formatting might be a way, but i think some
code
may be needed.

Can anyone help?

Thanks,

Roger

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in message
...
Excellent Don.

Thanks very much for this

Best regards, Roger



"Don Guillett" wrote:


Private Sub Worksheet_Change(ByVal Target As Range)

if target.address<"$B$1" then exit sub
'don't forget $ or " " and use CAPS for cell address

On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in
message
...
Hi Don,

Nice - it works well.

by the way, how would i restrict this to cell "b1"?

Thanks,

Roger



"Don Guillett" wrote:

Right click sheet tabview codecopy/paste this. You may want to
restrict
the cells involved.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in
message
...
Is it possible to enter a color number (1-56 for example) and make
that
cell
the color in question?

I am guessing conditional formatting might be a way, but i think
some
code
may be needed.

Can anyone help?

Thanks,

Roger

Roger,

I too am looking to do conditional formatting for 5 colors, or more. I saw
this which you posted. I have never done VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub

I did find the following VBA but I made 2 substitutions: 1) an array instead
of a1:a10; 2) the numbers. PROBLEM: When I tested numbers, not all the
colors appears, no light green (#35). Second problem, when I opened the
file, I said enable macros which changed the colors but once inside the
sheet, changing the number in the array didn't change the colors.

I NEED ALL COLORS TO CHANGE AS A VALUE CHANGES IN THE TARGET CELL.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("T4:Z5")) Is Nothing Then
Select Case Target
Case Is <= 0.00000025
icolor = 4
Case 0.000001 To 0.0000002495
icolor = 35
Case 0.00001 To 0.0000995
icolor = 2
Case 0.0001 To 0.000995
icolor = 6
Case Is = 0.0001
icolor = 3
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

I'm on an important project!

THANK YOU,

Phyllis

"Don Guillett" wrote:

Right click sheet tabview codecopy/paste this. You may want to restrict
the cells involved.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in message
...
Is it possible to enter a color number (1-56 for example) and make that
cell
the color in question?

I am guessing conditional formatting might be a way, but i think some code
may be needed.

Can anyone help?

Thanks,

Roger

It appears that you need to line up your 000000000000000 better

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PA Simon" <PA wrote in message
...
Roger,

I too am looking to do conditional formatting for 5 colors, or more. I
saw
this which you posted. I have never done VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub

I did find the following VBA but I made 2 substitutions: 1) an array
instead
of a1:a10; 2) the numbers. PROBLEM: When I tested numbers, not all the
colors appears, no light green (#35). Second problem, when I opened the
file, I said enable macros which changed the colors but once inside the
sheet, changing the number in the array didn't change the colors.

I NEED ALL COLORS TO CHANGE AS A VALUE CHANGES IN THE TARGET CELL.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("T4:Z5")) Is Nothing Then
Select Case Target
Case Is <= 0.00000025
icolor = 4
Case 0.000001 To 0.0000002495
icolor = 35
Case 0.00001 To 0.0000995
icolor = 2
Case 0.0001 To 0.000995
icolor = 6
Case Is = 0.0001
icolor = 3
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

I'm on an important project!

THANK YOU,

Phyllis

"Don Guillett" wrote:

Right click sheet tabview codecopy/paste this. You may want to restrict
the cells involved.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If IsNumeric(Target) Then
Target.Interior.ColorIndex = Target.Value
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in
message
...
Is it possible to enter a color number (1-56 for example) and make that
cell
the color in question?

I am guessing conditional formatting might be a way, but i think some
code
may be needed.

Can anyone help?

Thanks,

Roger