Making a cell a color using color numbers

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
R

Roger on Excel

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 tab>view code>copy/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
 
Hi Don,

Nice - it works well.

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Macro to automatically go to the right of the last colored cell 0
How to blank the colored cells? 0
Number Of Cells Between Colored Cells? 1
conditional format - table - cell empty return default color 1
Conditional Formatting with VBA 4
creating rows of colored cells from adjacent numbers 8
Excel VBA 1
Excel VBA - counting cells by colors and names to it in two different rows. 0

Back
Top