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 :
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

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" wrote in message
...
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 :
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

On Sun, 25 Jan 2004 12:39:50 -0000 "Bob Phillips"
wrote:

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


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


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

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

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" wrote in message
...
On Sun, 25 Jan 2004 12:39:50 -0000 "Bob Phillips"
wrote:

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

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 ,
"Bob Phillips" wrote:

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