Set the color of grid depend on variable

[Radoslaw Krzyzan]

I'd like to set the color of cells (sheet1)

[A1...A8] -> Red
[A9...A28] -> Green
[A29...A44] -> Blue
[A45...A54] -> Red
....

based on data from sheet2

Start Stop Color
1 8 Red
9 28 Green
29 44 Blue
45 54 Red
.....




How to do it ?

--




~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Radoslaw Krzyzan , pl.communicator@radek
http://www.communicator.pl , ICQ 4336523
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Frank Kabel]

Hi
for only three colors have a look at 'format - conditional Format'. For
more than 3 conditions you need VBA

 

[Don Guillett]

If you set up a table like this where col a-B is the range and col c has the
color index NUMBER.
1 8 3
9 28 4
29 44 37
45 54 3

Then, this will work

Sub setcolor()
For Each c In range("a1:a4")
x = c.Offset(0,1)
Sheets("sheet1").Range("c" & c & ":c" & x) _
..Interior.ColorIndex = c.Offset(, 2)
Next c
End Sub

 

[Debra Dalgleish]

You can use a Worksheet_Change event to change the colours. The
following code should go on the code module for Sheet2. Right-click the
sheet tab, and choose View Code. Paste the code where the cursor is
flashing.

'===============================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Dim c As Range
Dim cColour As Integer
Dim r As Long
Dim ws1 As Worksheet
Const RED = 3
Const BLUE = 5
Const GREEN = 10

r = Cells(Rows.Count, 1).End(xlUp).Row
Set ws1 = Worksheets("Sheet1")
ws1.Columns(1).Interior.ColorIndex = 0
For Each c In Range(Cells(2, 1), Cells(r, 1))
Select Case UCase(c.Offset(0, 2).Value)
Case "RED": cColour = RED
Case "BLUE": cColour = BLUE
Case "GREEN": cColour = GREEN
Case Else: cColour = 0
End Select

ws1.Range(ws1.Cells(c.Value, 1), ws1.Cells(c.Offset(0, 1).Value, 1)) _
.Interior.ColorIndex = cColour
Next c
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & "--" & Err.Description
Exit Sub

End Sub
'=========================================

I'd like to set the color of cells (sheet1)

[A1...A8] -> Red
[A9...A28] -> Green
[A29...A44] -> Blue
[A45...A54] -> Red
...

based on data from sheet2

Start Stop Color
1 8 Red
9 28 Green
29 44 Blue
45 54 Red
....




How to do it ?

--




~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Radoslaw Krzyzan , pl.communicator@radek
http://www.communicator.pl , ICQ 4336523
~~~~~~~~~~~~~~~~~~~~~~~~~~~~