Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Changing colour of text in cell

49 views
Skip to first unread message

Steve Lowe

unread,
Jan 25, 2004, 5:56:49 AM1/25/04
to
Hi,

I'm slowly learning Excel Macro programming and would appreciate some
help with the following.

I have a Excel spreadsheet that is used at certain times of the day to
record temperature readings.

I have a input box that asks for the temperature and then puts the
value in the active cell.

The macro then automatically adds the date and time of the reading.

Each reading is on a separate row in the sheet.

What I would like to try and do is have the readings for each of the
days in the week in separate colours ie. Monday = blue, Tuesday =
Green etc.

Rather that do this manually is there any way that my macro can
include some code that changes or applies the colours to the data that
has just been entered using the value of the day of the week.

Thanks if you can offer me some guidance.

Regards

SpLoWe


- Steve Lowe
- E-Mail : NO.SPAM...@usa.net
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

Bob Phillips

unread,
Jan 25, 2004, 7:39:50 AM1/25/04
to
Steve,

It's a bit difficult without seeing your code, but here is an example that
gets the input, creates the timestamp, and colours it

Dim myVal

myVal = InputBox("Supply reading")
If myVal <> "" Then
With ActiveCell
.Value = myVal
.Offset(0, 1) = Now
.Offset(0, 1).NumberFormat = "dd mm yyyy hh:mm:ss"
With .Offset(0, 1)
Select Case Weekday(.Value)
Case 1: .Font.ColorIndex = 10 'green
Case 2: .Font.ColorIndex = 5 'blue
Case 3: .Font.ColorIndex = 1 'black
Case 4: .Font.ColorIndex = 6 'yellow
Case 5: .Font.ColorIndex = 21 'violet
Case 6: .Font.ColorIndex = 3 'red
Case 7: .Font.ColorIndex = 8 'turquoise
End Select
End With
End With
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve Lowe" <spl...@usa.net> wrote in message
news:c78710duaca8n8lbe...@4ax.com...

Steve Lowe

unread,
Jan 25, 2004, 9:28:09 AM1/25/04
to


Hi Bob,

Thanks for that - it got me heading in the right direction, made a few
amendments to give the same colour to other cells in the row
Sub DataEntry()

Dim myVal, MyDay, DayCol

Application.Goto Reference:="InputPoint"
Selection.EntireRow.Insert
ActiveCell.Offset(0, 0).Activate


myVal = InputBox("Enter Reading : ")


If myVal <> "" Then
With ActiveCell
.Value = myVal

.Offset(0, -4) = Now
.Offset(0, -4).NumberFormat = "dd/mm/yy"
.Offset(0, -1) = Now
.Offset(0, -1).NumberFormat = "hh:mm"
With .Offset(0, -4)
Select Case Weekday(.Value)
Case 1: MyDay = "Sun"
Case 2: MyDay = "Mon"
Case 3: MyDay = "Tue"
Case 4: MyDay = "Wed"
Case 5: MyDay = "Thur"
Case 6: MyDay = "Fri"
Case 7: MyDay = "Sat"
End Select
End With



With .Offset(0, -4)
Select Case Weekday(.Value)
Case 1: DayCol = 10 'green
Case 2: DayCol = 5 'blue
Case 3: DayCol = 1 'black
Case 4: DayCol = 6 'yellow
Case 5: DayCol = 21 'violet
Case 6: DayCol = 3 'red
Case 7: DayCol = 8 'turquoise
End Select
.Font.ColorIndex = DayCol
End With

ActiveCell.Offset(0, -2) = MyDay
ActiveCell.Offset(0, -2).Font.ColorIndex = DayCol
ActiveCell.Offset(0, -1).Font.ColorIndex = DayCol
ActiveCell.Offset(0, -0).Font.ColorIndex = DayCol
End With
End If
End Sub

Bob Phillips

unread,
Jan 25, 2004, 9:34:10 AM1/25/04
to

> Hi Bob,
>
> Thanks for that - it got me heading in the right direction, made a few
> amendments to give the same colour to other cells in the row

Hi Steve,

That's great. Glad to assist, and thanks for letting me know.

Regards

Bob


Bob Phillips

unread,
Jan 25, 2004, 9:56:46 AM1/25/04
to
Steve,

Just a though, you can combine some of this code and simplify it

Sub DataEntry()
Dim myVal, MyDay, DayCol

'Application.Goto Reference:="InputPoint"
Selection.EntireRow.Insert
ActiveCell.Offset(0, 0).Activate

myVal = InputBox("Enter Reading : ")
If myVal <> "" Then
With ActiveCell
.Value = myVal
.Offset(0, -4) = Now
.Offset(0, -4).NumberFormat = "dd/mm/yy"
.Offset(0, -1) = Now
.Offset(0, -1).NumberFormat = "hh:mm"
With .Offset(0, -4)
Select Case Weekday(.Value)

Case 1: DayCol = 10 'green
Case 2: DayCol = 5 'blue
Case 3: DayCol = 1 'black
Case 4: DayCol = 6 'yellow
Case 5: DayCol = 21 'violet
Case 6: DayCol = 3 'red
Case 7: DayCol = 8 'turquoise
End Select

.Font.ColorIndex = DayCol
End With

.Offset(0, -2) = Format(.Offset(0, -4).Value, "ddd")
.Offset(0, -2).Resize(1, 3).Font.ColorIndex = DayCol


End With
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve Lowe" <spl...@usa.net> wrote in message

news:7ck7101bf54enpmhr...@4ax.com...

JE McGimpsey

unread,
Jan 25, 2004, 2:35:23 PM1/25/04
to
Even a bit more simple, and puts the Date and Time in the appropriate
cells rather than both getting a date/time.

Public Sub DataEntry()
Dim myVal As Variant
Dim dayCol As Long



'Application.Goto Reference:="InputPoint"
Selection.EntireRow.Insert
'ActiveCell.Offset(0, 0).Activate

myVal = InputBox("Enter Reading : ")
If myVal <> "" Then
With ActiveCell
.Value = myVal

With .Offset(0, -1)
.Value = Time
.NumberFormat = "hh:mm"


End With
With .Offset(0, -4)

.Value = Date
.NumberFormat = "dd/mm/yy"
dayCol = Choose(WeekDay(.Value), _
10, 5, 1, 6, 21, 3, 8)
.Font.ColorIndex = dayCol
End With
With .Offset(0, -2)
.Value = Format(Date, "ddd")
.Resize(1, 3).Font.ColorIndex = dayCol
End With


End With
End If
End Sub


In article <ensn1N14...@TK2MSFTNGP11.phx.gbl>,
"Bob Phillips" <bob.ph...@notheretiscali.co.uk> wrote:

> Steve,
>

0 new messages