Change auto shape colour on cell value

[bennygod]

Hi all, I was wondering if you can help. I have created a strain gauge bridge calculator. The bridge has 4 resistors, I have also added a picture of the bridge using autoshapes and have 4 rectangles to represent each resistor (rectangle1,rectangle2,rectangle3,rectangle4).

I want to add a shunt resistor across just one of the resistors in parallel and this will effectively change the resistance of that leg of the bridge. I want the rectangle that represents the resistor that has the shunt to change colour from white to say red to show which one has been shunted.

I have numbered my resistors R1,R2,R3,R4. I have a cell (Say A1) with a drop down list that lets you pick either "R1", "R2","R3","R4". by picking changes my calculator so it knows which resistor has had the shunt applied.

Finally I also have a cell (say A2) which lets you input the shunt resistor value. If the value is 0, then the shunt is not applied in my calculations.

So I need to change the colour of a particular rectangle based on the value picked in A1 but it must also depend on A2>0.

Can anyone help, i'm not great on visual basic, but if you can show me with a description I would be grateful.

 

[bennygod]

Also, the next step that i'd like to take would be to add a button that allows me to switch on and off the shunt resistor (so even if my cell A2>0, I can make the calculation and resistor shading ignore it).

 

[MickG]

Try this:-
Right Click Sheet Tab, Select "View Code", VB Window Appears.
Paste Code into VB Window.
Close Window
To run code Select value in "A1" (R1,R2,R3,or R4)

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" And Range("A2").value > 0 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] R = 1 To 4
    [COLOR="Navy"]If[/COLOR] Right(Target, 1) = R [COLOR="Navy"]Then[/COLOR]
        ActiveSheet.Shapes("Rectangle " & R).Fill.ForeColor.SchemeColor = 2
    [COLOR="Navy"]Else[/COLOR]
        ActiveSheet.Shapes("Rectangle " & R).Fill.ForeColor.SchemeColor = 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[bennygod]

Thanks Mick

That really helped! Now I've got up to speed a little on VBA and have got it all working. I've also got a VBA button that turns on and off my shunt resistor. I want to password protect my sheet so that users can only enter information into certain cells and obviously press the shunt on/off button.

Curently the start and end of my VBA code is:


Private Sub CommandButton1_Click()
ActiveSheet.Unprotect

......

ActiveSheet.Protect

End Sub



this enables me to unprotect the sheet to run the code to change my colours and then reprotect it. However it does not password protect the sheet. How would I go about adding this in so that it password protected/unprotected the sheet to run the shunt button, but without user need to enter the password to run the button?

Thanks, appreciate any help