J
Jim Allen
Hi,
I am very new to Excel macro programming, but thanks to Dave
McRitchie's code (as well as some contributions from numerous others,
I have a macro which works, but not quite as I intended.
What I want to do is create a gantt chart in excel 2000. Each altenate
row has a project name and details, with a range of columns
representing weeks in the financial year. I would like to enter a
value into a cell to represent the stage of a project (e.g. sketch
scheme, detail design, tender, contruction, post-tender). I would like
the macro to change the background colour of the cell to produce the
chart. This works, but I am still left with the text visible in the
cells, but strangely, only for some values! I would like to change the
text colour to match the background colour, so all you can see is just
a single coloured cell despite the fact that it contains the text
which drives the macro.
The macro I am using is
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim cell As Range
Dim charArr As Variant
Dim colorArr As Variant
Dim i As Long
'Substitute your preferred characters here:
charArr = Array("s", "d", "t", "c", "c-", "pc")
'Substitute your preferred colorindexes and order here:
colorArr = Array(3, 4, 5, 6, 7, 8)
For Each cell In Target
With cell
.Interior.ColorIndex = xlColorIndexNone
For i = 0 To UBound(charArr)
If .Value = charArr(i) Then
.Interior.ColorIndex = colorArr(i)
' .Font.ColorIndex = colorArr(i)
' *** the above line is my efort, which doesn't work! ***
Exit For
End If
Next i
End With
Next cell
End Sub
This is unashamedly Dave McRitchie's code (thanks a lot Dave!)and it
works, but I can't work out how to change the font colour of the cells
to match.
I have tried adding a line which I thought might change the font
colour, but it didn't work. I have tried using anothe For - Next loop
to change the text, but this doesn't seem to work either.
I'm sure I'm being stupid, but can anyone give me any idea on what I'm
doing wrong?
Regards,
Jim Allen
I am very new to Excel macro programming, but thanks to Dave
McRitchie's code (as well as some contributions from numerous others,
I have a macro which works, but not quite as I intended.
What I want to do is create a gantt chart in excel 2000. Each altenate
row has a project name and details, with a range of columns
representing weeks in the financial year. I would like to enter a
value into a cell to represent the stage of a project (e.g. sketch
scheme, detail design, tender, contruction, post-tender). I would like
the macro to change the background colour of the cell to produce the
chart. This works, but I am still left with the text visible in the
cells, but strangely, only for some values! I would like to change the
text colour to match the background colour, so all you can see is just
a single coloured cell despite the fact that it contains the text
which drives the macro.
The macro I am using is
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim cell As Range
Dim charArr As Variant
Dim colorArr As Variant
Dim i As Long
'Substitute your preferred characters here:
charArr = Array("s", "d", "t", "c", "c-", "pc")
'Substitute your preferred colorindexes and order here:
colorArr = Array(3, 4, 5, 6, 7, 8)
For Each cell In Target
With cell
.Interior.ColorIndex = xlColorIndexNone
For i = 0 To UBound(charArr)
If .Value = charArr(i) Then
.Interior.ColorIndex = colorArr(i)
' .Font.ColorIndex = colorArr(i)
' *** the above line is my efort, which doesn't work! ***
Exit For
End If
Next i
End With
Next cell
End Sub
This is unashamedly Dave McRitchie's code (thanks a lot Dave!)and it
works, but I can't work out how to change the font colour of the cells
to match.
I have tried adding a line which I thought might change the font
colour, but it didn't work. I have tried using anothe For - Next loop
to change the text, but this doesn't seem to work either.
I'm sure I'm being stupid, but can anyone give me any idea on what I'm
doing wrong?
Regards,
Jim Allen