lundi 20 juin 2016

VBA Want to turn cells that don't match red

I am working on a form to send out to agencies and for this particular sheet all the totals are suppose to be equal to each other. I used If statements to check if the totals are all equal however when using Or to connect the variables,in the IF statement, one mistake in the totals makes all the fields red so the user can't quickly find their mistake. Using And to connect the variables also leads to a bug because if there is an error in 2 separate totals it won't show an error at all. When a button is pressed I want the program to compare all totals and make only the ones that don't match the others red, is there anyway for me to do that? Its more of a logic questions but here is my code:

Sheets("Initiated Proceedings").Select
Sheet3.Unprotect


DisputantTotal = Range("I19").Value
DisputantFrequency = Range("I24").Value
GenderTotal = Range("I31").Value
AgeTotal = Range("I39").Value
EthnicityTotal = Range("I50").Value
LanguageTotal = Range("I65").Value
IncomeTotal = Range("I73").Value
DisabilityTotal = Range("I79").Value
HomelessTotal = Range("I85").Value


If DisputantTotal <> DisputantFrequency Or DisputantTotal <> GenderTotal Or DisputantTotal <> AgeTotal Or DisputantTotal <> EthnicityTotal Or DisputantTotal <> LanguageTotal Or DisputantTotal <> IncomeTotal Or DisputantTotal <> HomelessTotal Or DisputantTotal = 0 Then



Range("I19").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Else

Range("I19").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End If


If DisputantTotal <> DisputantFrequency Or DisputantFrequency <> GenderTotal Or DisputantFrequency <> AgeTotal Or DisputantFrequency <> EthnicityTotal Or DisputantFrequency <> LanguageTotal Or DisputantFrequency <> IncomeTotal Or DisputantFrequency <> HomelessTotal Or DisputantFrequency = 0 Then



Range("I24").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Else

Range("I24").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End If



If DisputantTotal <> GenderTotal Or DisputantFrequency <> GenderTotal Or GenderTotal <> AgeTotal Or GenderTotal <> EthnicityTotal Or GenderTotal <> LanguageTotal Or GenderTotal <> IncomeTotal Or GenderTotal <> HomelessTotal Or GenderTotal = 0 Then



Range("I31").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Else

Range("I31").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End If



If DisputantTotal <> AgeTotal Or DisputantFrequency <> AgeTotal Or GenderTotal <> EthnicityTotal Or AgeTotal <> LanguageTotal Or AgeTotal <> IncomeTotal Or AgeTotal <> HomelessTotal Or AgeTotal = 0 Then



Range("I39").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Else

Range("I39").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End If


If DisputantTotal <> EthnicityTotal Or DisputantFrequency <> EthnicityTotal Or EthnicityTotal <> AgeTotal Or EthnicityTotal <> LanguageTotal Or EthnicityTotal <> IncomeTotal Or EthnicityTotal <> HomelessTotal Or EthnicityTotal = 0 Then



Range("I50").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Else

Range("I50").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End If


If DisputantTotal <> LanguageTotal Or DisputantFrequency <> LanguageTotal Or LanguageTotal <> AgeTotal Or LanguageTotal <> EthnicityTotal Or LanguageTotal <> IncomeTotal Or LanguageTotal <> HomelessTotal Or LanguageTotal = 0 Then



Range("I65").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Else

Range("I65").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End If



If DisputantTotal <> IncomeTotal Or DisputantFrequency <> IncomeTotal Or IncomeTotal <> AgeTotal Or IncomeTotal <> EthnicityTotal Or IncomeTotal <> HomelessTotal Or IncomeTotal = 0 Then



Range("I73").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Else

Range("I73").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End If


If DisputantTotal <> HomelessTotal Or DisputantFrequency <> HomelessTotal Or HomelessTotal <> AgeTotal Or HomelessTotal <> EthnicityTotal Or IncomeTotal <> HomelessTotal Or HomelessTotal = 0 Then



Range("I85").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Else

Range("I85").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End If








ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowSorting:= _
        True, AllowFiltering:=True, AllowUsingPivotTables:=True


Range("I85").Select


End Sub

BTW the user presses a button to make the macro run.

Thanks

Aucun commentaire:

Enregistrer un commentaire