Help!- keep tripping up on a colour

[out of my depth]

Hi all

Borrowed a friends code to move some cells when you click a live button and
then on a crytalise button. The 'live' button changes colour when you are
ready and clicking the crystalise button should reset the colour and move a
row of numbers into another spreadsheet.
keep tripping up on the following;
If liveButton.Fill.ForeColor.SchemeColor = 13 Then

Anyone have any ideas? I've included the whole code below;

im thisShape As Shape
Set thisShape = ActiveSheet.Shapes(Application.Caller)
Dim r As Range
Set r = thisShape.TopLeftCell
'check that this is a row that we have made live
Dim liveButton As Shape
For Each liveButton In ActiveSheet.Shapes
If liveButton.TopLeftCell.Row = r.Row And
liveButton.TopLeftCell.Column = 14 Then
GoTo checkColour
End If
Next liveButton
checkColour:
If liveButton.Fill.ForeColor.SchemeColor = 13 Then
Exit Sub
End If

'check that we have all the required entries
If Not (checkCellValue(Cells(r.Row, 6), "Stop/Exit $")) Then
Exit Sub
End If

'find the pnl row
Dim firstCellInPnLRow As Range
Set firstCellInPnLRow = Cells(25, 5)
While firstCellInPnLRow.Value <> Cells(r.Row, 5).Value
Set firstCellInPnLRow = firstCellInPnLRow.Offset(1, 0)
If (firstCellInPnLRow.Value = "") Then
MsgBox ("Can't find entry in PnL table for this instrument")
Exit Sub
End If
Wend

'find the trade log entry for this row
Dim logSheet As Worksheet
Set logSheet = ActiveWorkbook.Sheets("Trade Log")
Dim firstCellInRow As Range
Set firstCellInRow = logSheet.Cells(2, 1)
Dim found As Boolean
found = False
While firstCellInRow.Value <> ""
If firstCellInRow.Value = Cells(r.Row, 17).Value Then
found = True
GoTo after
End If
Set firstCellInRow = firstCellInRow.Offset(1, 0)
Wend
after:
If found = False Then
MsgBox "Can't find log entry for this row"
Exit Sub
End If

'update the trade log entry
firstCellInRow.Offset(0, 2).Value = Date
firstCellInRow.Offset(0, 7).Value = Cells(r.Row, 10)

'update the pnl
firstCellInPnLRow.Offset(0, 1).Value = firstCellInPnLRow.Offset(0,
1).Value + Cells(r.Row, 6).Value

'look up the fx rate
Dim cur As String
cur = firstCellInRow.Offset(0, 12).Value
Set logSheet = ActiveWorkbook.Sheets("Reference Data")
Dim referenceTableRow As Range
Set referenceTableRow = logSheet.Cells(3, 12)
While (Not referenceTableRow.Value = "" And Not referenceTableRow.Value
= cur)
Set referenceTableRow = referenceTableRow.Offset(1, 0)
Wend
If (referenceTableRow.Value = "" Or referenceTableRow.Offset(0, 1).Value
= "") Then
MsgBox "Can't find fxrate for " + cur
End If
firstCellInRow.Offset(0, 13) = referenceTableRow.Offset(0, 1).Value

'clear the row ready for a new trade
Call clear

End Sub


I am trying to run this on excel 03 and I'm not sure what this was written
on -would that make a difference?

If anyone has any thoughts I would be most grateful!!!

 

[Bernie Deitrick]

Your code:

If liveButton.Fill.ForeColor.SchemeColor = 13 Then
Exit Sub
End If

is trying to address an object that no longer exists, since it is calling liveButton outside the
for/next livebutton loop. Move that code inside the loop, and livebutton will still exist. Perhaps:

For Each liveButton In ActiveSheet.Shapes
If Not (liveButton.TopLeftCell.Row = r.Row And liveButton.TopLeftCell.Column = 14) Then
GoTo DontCheckColour
If liveButton.Fill.ForeColor.SchemeColor = 13 Then
Exit Sub
End If
DontCheckColour:
End If
Next liveButton


HTH,
Bernie
MS Excel MVP