Colouring Excel Cells

[LITHIA]

Hi,

I need to be able to select a range of cells, and then press a button in a toolbox I have created.

When you press the button, the code goes through every cell selected, gets its value and changes its ColorIndex to the value in the cell.

But I have a problem...

I currently have this code:

Selection.Interior.ColorIndex = ActiveCell.Value

which works okay for singular cells. But if you select a range of more than 1 cell, it colours the entire range by the first number in it.

Can anyone help me get it so it will go through each cell individually and change each one to its appropiate colour?

Thanks very much! This will be a real help, I'm not sure how I would code such a thing yet.
I have never used "For" before which will probably be needed? And I'm not very good at arrays if those are needed either. Thanks again

[Jason Badon]

HI,
By you description I gave it a try an it worked ok for me. If you can send me you workbook and the code you are using so I can see your problem and try to help you find a solution.


Jason

[LITHIA]

okay...

if u got cells like this

1
2
3
4

and select the cells, 1 > 4 so they are all highlighted, then press the button, all of them go black which is the colour of 1

each cell doesn't go the individual colour of each value.

you sure thats what you thought i meant?

[Spajeoly]

Just run through the cells with a loop.

For i = 1 To 1000
  If Range("$A$" & i).Text = "" Then Exit Sub
  
     Range("$A$" & i).Interior.Color = Range("$A$" & i).Text
Next

[LITHIA]

lol sorry but i have no idea how that was suppost to have worked....

either got an error with the code about it not being able to set its color index, or it would just colour the entire range the first colour again... or do nothing at all which happened most of the time...

there has to be a simple way about this. I don't understand why you have this "$A$" business...

[RobDog888]

I wrote this a while ago to round numbers in a highlighted range.
I know you can modify it to change the color instead of rounding.
It works better to use the IsEmpty rather than the = ""

Private Sub Workbook_Open()
 
    Dim i As Integer
    Dim ii As Integer
    
    areaCount = Application.Selection.Areas.Count
    If areaCount <= 1 Then
        MsgBox "The selection contains " & Application.Selection.Columns.Count & " columns."
        MsgBox "The selection contains " & Application.Selection.Rows.Count & " rows."
    Else
        For i = 1 To areaCount
            MsgBox "Area " & i & " of the selection contains " & Application.Selection.Areas(i).Columns.Count & " columns."
            MsgBox "Area " & i & " of the selection contains " & Application.Selection.Areas(i).Rows.Count & " rows."
        Next i
    End If
    Set xRange = Application.ActiveCell.CurrentRegion
    c = xRange.Columns(xRange.Columns.Count).Column
    c = Chr(64 + c)
    b = xRange.Columns(1).Column
    b = Chr(64 + b)
    MsgBox "First column = " & b
    MsgBox "First row = " & xRange.Rows(1).Row
    MsgBox "Last column = " & c
    MsgBox "Last row = " & xRange.Rows(xRange.Rows.Count).Row
    For i = xRange.Rows(1).Row To xRange.Rows(xRange.Rows.Count).Row
        For ii = xRange.Columns(1).Column To xRange.Columns(xRange.Columns.Count).Column
            b = xRange.Columns(ii).Column
            b = Chr(64 + b)
            CellValue = Worksheets("Sheet1").Range(b & i).Value
            If IsEmpty(CellValue) = False Then
                Worksheets("Sheet1").Range(b & i).Value = Round(CellValue, 0)
            End If
        Next
    Next
    
End Sub

[machinist]

Okay Lithia,

Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
If ActiveCell.Value = 1 Then a
If ActiveCell.Value = 2 Then b
If ActiveCell.Value = 3 Then c
If ActiveCell.Value = 4 Then d
Exit Sub
ErrorHandler: MsgBox "Mean Program"
End Sub
Private Sub a()
If ActiveCell.Value = 1 Then ActiveCell.Value = 3
Selection.Interior.ColorIndex = ActiveCell.Value
If ActiveCell.Value = 3 Then ActiveCell.Value = 1
End Sub

Private Sub b()
If ActiveCell.Value = 2 Then ActiveCell.Value = 44
Selection.Interior.ColorIndex = ActiveCell.Value
If ActiveCell.Value = 44 Then ActiveCell.Value = 2
End Sub

Private Sub c()
If ActiveCell.Value = 3 Then ActiveCell.Value = 8
Selection.Interior.ColorIndex = ActiveCell.Value
If ActiveCell.Value = 8 Then ActiveCell.Value = 3
End Sub

Private Sub d()
If ActiveCell.Value = 4 Then ActiveCell.Value = 5
Selection.Interior.ColorIndex = ActiveCell.Value
If ActiveCell.Value = 5 Then ActiveCell.Value = 4
End Sub

Oh biology next.