VBA IF Loop Help

bdkrame

New Member
Joined
Aug 26, 2015
Messages
9
I'm having a bit of trouble with a macro. The code is supposed to first check to see if a certain parameter called 'Setup' is True or False and then checks each sheet for the value of R43 if 'Setup' is True or R44 if 'Setup' is False. If the value in R43 or R44 is True the code highlights a cell on a different tab called 'Input' green, or red if the value is False. This is done for different cells corresponding to each worksheet checked. Below is my code.

Code:
Sub Tab_Change(sheet As String)
' The sheet name is provided by the calling macro on each sheet.
'   Determines if in setup mode or not.
    If Worksheets("Input").Setup.Value = True Then
        If Worksheets(sheet).Range("r43").Value = True Then
            green (sheet)
        Else
            Red (sheet)
        End If
    Else
        If Worksheets(sheet).Range("r44").Value = True Then
            green (sheet)
        Else
            Red (sheet)
        End If
    End If


End Sub


Sub Red(sheet As String)
    Dim changedcell As String
    changedcell = sheet + "cell"


    'Sets sheet indicator to Red
    'Worksheets("Input").Shapes(sheet).Fill.ForeColor.RGB = RGB(255, 0, 0)
    Worksheets("Input").Range(changedcell) = False


End Sub


Sub green(sheet As String)
    Dim changedcell As String
    changedcell = sheet + "cell"


    'Sets sheet indicator to Green
    'Worksheets("Input").Shapes(sheet).Fill.ForeColor.RGB = RGB(0, 128, 0)
    Worksheets("Input").Range(changedcell) = True


End Sub
My problem is that if R43 is TRUE and R44 is FALSE and the Setup.Value = TRUE, I should be checking R43 and coloring my cell green but for some reason I am seeing red. It seems like the code is looking at cell R44 and seeing the FALSE value, but I can't understand why. Anybody have any ideas?
 

bdkrame

New Member
Joined
Aug 26, 2015
Messages
9
That turned all of my cells red. I took out the second if loop that checks R44 and it correctly colors the cell according to the value of R43. As soon as I add it back in it changes the result. Is the code executing both halves of my overall if loop? That would make the error I'm seeing make sense, but I don't see why it would do that.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,777
Neither the ObjectBrowser nor Microsoft's Development website show a .SetUp property for a worksheet object.

What is the .SetUp property that you are testing for?
 

Forum statistics

Threads
1,081,576
Messages
5,359,729
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top