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
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...
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
Hi Steve,
That's great. Glad to assist, and thanks for letting me know.
Regards
Bob
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...
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,
>