Adding subtraction formula in code 1

[nelson97]

Hi, please review code below. I would like the line with the * to subtract 1 from the value in Z2 when the command control is clicked.
With Sheets("Sheet3").Range("Z2")
With Sheets("Sheet1").Range("C7")
If .Interior.ColorIndex = 1 Then
.Interior.ColorIndex = 15
Else
.Interior.ColorIndex = 1
End If
End With
*** .Value = (Z2-1)
End With

Thanks

 

[Quandan]

I'd say this should work:

.Value = .Value - 1

Kind regards,


Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - Dutch supportsite for spreadsheetusers)

http://www.quandan.nl

 

[Zathras]

Which "Z2" do you want to subtract 1 from: The one on Sheet1 or the one on Sheet3? Where do you want the result to go: C7 on Sheet1 or Z2 on Sheet3?

Your second "With" (Sheet1) overrides the first one with the result that you are toggling the background color in Sheet1!C7 and doing nothing on Sheet3. (But of course you know that since you surely have run your code before posting here.)

Can you be a little more clear about what you are trying to do?

 

[nelson97]

Hi Zathras, Quandan has answered my question. I was essentially looking for the .value = .value-1 syntax

Thanks