Having troubl with IF and Else IF

[mgquedu]

Hi All, I have the below code but I keep getting the following error message: "Complie Error, Must be first statement on the line"
I'm new to vba, can someone explain what I am doing wrong! Thank you.

Sub Chart_Background()
    
    If Cells(4, 4).Value > 0.8 Then
    ActiveSheet.ChartObjects("Chart 1").Activate
        With ActiveSheet.Shapes("Chart 1").Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
            
      Else If Cells(4, 4) > 0.5 Then
     With ActiveSheet.Shapes("Chart 1").Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 255, 0)
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
       Else
      With ActiveSheet.Shapes("Chart 1").Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 255, 0)
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
            End With
    End If
End Sub
[CODE]

 

[Jonmo1]

I believe Else If should be ElseIf (one word)

 

[mgquedu]

Thanks! I figured it out, I actually did three seperate If's and it works. Do you know how to make the code update automatically refresh when the value it's looking at changes?

 

[Jonmo1]

How does it change? manual entry by hand? Or is it a formula?

And is it just that one cell, or do you want to expand it to look at many cells in a particular range?

 

[DocAElstein]

Wot about this


<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Chart_Background()<br><br>     <SPAN style="color:#00007F">If</SPAN> Cells(4, 4).Value > 0.8 <SPAN style="color:#00007F">Then</SPAN><br>       ActiveSheet.ChartObjects("Chart 1").Activate<br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Shapes("Chart 1").Fill<br>        .Visible = msoTrue<br>        .ForeColor.RGB = RGB(255, 0, 0)<br>        .ForeColor.TintAndShade = 0<br>        .ForeColor.Brightness = 0<br>        .Transparency = 0<br>        .Solid<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>     <SPAN style="color:#00007F">ElseIf</SPAN> Cells(4, 4) > 0.5 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Shapes("Chart 1").Fill<br>        .Visible = msoTrue<br>        .ForeColor.RGB = RGB(255, 255, 0)<br>        .ForeColor.TintAndShade = 0<br>        .ForeColor.Brightness = 0<br>        .Transparency = 0<br>        .Solid<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>     <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Shapes("Chart 1").Fill<br>        .Visible = msoTrue<br>        .ForeColor.RGB = RGB(0, 255, 0)<br>        .ForeColor.TintAndShade = 0<br>        .ForeColor.Brightness = 0<br>        .Transparency = 0<br>        .Solid<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>     <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mgquedu]

That works! I did 3 seperate if's which ended up working as well. Thank you for below! Would you know how to make this code refresh automatically when the cell it's looking at changes?

 

[Jonmo1]

Would you know how to make this code refresh automatically when the cell it's looking at changes?

Did you see the questions I asked in Post #4 ?

 

[DocAElstein]

Hi . I am a beginner myself. You need to get into Worksheet_change stuff. A code like the following needs to be placed in the code in a sheet, (not in a Module):




<font face=Calibri><br><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Err <SPAN style="color:#007F00">' Just in case it does not work, this ends the VBA</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CellsYouChange <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">' The cell or cells you are interested in changing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> CellsYouChange = Range("D4") <SPAN style="color:#007F00">' I think you arte interseted in cell(4,4) or "D4"</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Intersect(CellsYouChange, Target) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">' Do nothing if what you typed in did not "Intersect" the cell your interested in. Or in normal English: Do Nothing if you did not type anything in the cell you are interested in!!</SPAN><br>        Else: MsgBox ("Hey - you typed something in") <SPAN style="color:#007F00">' Insted of MsgBox, you could type in your entire code here!</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>Err:     MsgBox ("F##k! - It did not work, Sorry")  <SPAN style="color:#007F00">' An error message from me, if it did not work!</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>









. I knocked up a complete .xlsm file for you. You get it by cliking here
FileSnack | Easy file sharing
. It is written in Excel 2007 (German Version).
. If you type in something in cell (4, 4) , (“D4”) , then see what happens, then look at the code… I think then you will get the point.

Alan Elston
Baveria
Germany

 

[DocAElstein]

Um.. I just remembered, people usually use "Zipped" Files when sending. ( I am new to the Whole Computer stuff!). So here is that File again in "Zipped" Form!






FileSnack | Easy file sharing



Alan

 

[DocAElstein]

....... So here is that File again in .....
Alan

EDIT. Just used this code in another Thread – Noticed / remembered that File snack has closed down so my earlier File links are not valid… here is the new link one via Box

https://app.box.com/s/ikq5cbrlfoxfd0bmgtzu2kqkm271mj37

Alan