Constantly Check If There Is a Match to Execute a Macro

Hi all,

I have this simple code that is acting in the background to check if the data validation list selection in Cell AB19 is "User Defined". It all basically formatting changes.

Now this macro is only acting on Cell AB19 where this data validation list is. But I have 10 more different rows each having its own data validation list that will follow the same check.

I am trying to transform this macro to act not only on Cell AB19 but also to act on the 10 other data validation lists on Rows 20 to Row 30 (AB20, AB21, AB22 etc.). I don't want to repeat this macro 10 times to check for each data validation.

Is there a tidier way to adjust this macro. Or maybe any other suggested code.

Any help is appreciated.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Range("AB19").Value = "User Defined" Then
    Range("AG19:AM19").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
    End With
    Range("AN19:AO19").Select
    With Selection.Interior
        .Color = 14803455
    End With
    Selection.Locked = False
Else
    Range("AG19:AM19").Select
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
    End With
    Range("AN19:AO19").Select
    With Selection.Interior
        .Pattern = xlNone
    End With
    Selection.Locked = True
End If
End Sub

Quote from royUK

Maybe

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Target
Select Case Target.Address
Case "AB19"
If .Value = "User Defined" Then
Range("AG19:AM19").Font.ThemeColor = xlThemeColorDark1
Range("AN19:AO19").Interior.Color = 14803455
.Locked = False
Else
Range("AG19:AM19").Font.ThemeColor = xlThemeColorLight1
Range("AN19:AO19").Interior.Pattern = xlNone
.Locked = True
End If
Case "AB20"
' etc
End Select
End Sub

Display More

Thanks for the input.

I cant seem to make your code run as intended. I am a beginner VBA user, I just compile code i find over google haha.

Actually now I'm thinking this approach wont work since the sheet will be locked and password protected.

My understanding this code will constantly check if there is a match whenever anything on the sheet is modified. But if that's true I'm not sure how this will work with a password protected sheet since the code will do the format changes whether there is a match or not.

Quote from royUK

My code will check if specific cells are changed. With the information you provided that's all I can do. The selcet case needs extending to cover your other cells.

If the sheet is protected then you need to unprotect it within the code, then protect it. Otherwise, you can protect withuserinterface only.

Does it work on AB19?

Hello again,

I removed the Case 20 part but the code wasn't making any changes based on AB19, I don't know why.

Actually I changed the approach and made 10 drop down combo boxes and linked the references cell with a macro. Seems to work and I think less trouble than having a macro run in the background everytime someone makes any change.

Thanks for your help Roy