Hi Peter,
Based partly on information from previous posts by you, try something like:
'===============
Private Sub Worksheet_Calculate()
Dim rng As Range
Dim i As Long
Dim rCell As Range
Const sAdd As String = "A100" '<<==== CHANGE
Const NumOfButtons As Long = 10 '<<==== CHANGE
Dim OleObj As OLEObject
Set rng = Me.Range(sAdd).Resize(NumOfButtons)
For i = 1 To NumOfButtons
With Me.OLEObjects("Commandbutton" & i).Object
If rng(i).Value < "" Then
.BackColor = &HFFFF&
Else
.BackColor = &HFF&
End If
End With
Next i
End Sub
'<<===============
---
Regards,
Norman
"peter.thompson"
<peter.thompson.20fg5m_1135230901.6389@excelforu m-nospam.com wrote in
message news:peter.thompson.20fg5m_1135230901.6389@excelfo rum-nospam.com...
I have set up a sheet that has 10 command buttons which change color
when worksheet cells contain something other than a ""
Here is the cosr I'm using too effect this
With ThisWorkbook.Sheets("Costs").CommandButton1
If Range("a100").Value < "" Then
BackColor = &HFFFF00
Else
BackColor = &HE0E0E0
End If
End With
With ThisWorkbook.Sheets("Costs").CommandButton2
If Range("a101").Value < "" Then
BackColor = &HFFFF00
Else
BackColor = &HE0E0E0
End If
End With
etc etc
Is there a more efficient way to do this?
Cheers
Peter (new to VBA)
--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=495425