How to use named ranged as reference for validation-CodePudding

Hoping I can get help here, I am currently using the Dim Long in my VBA code but since I am referring to multiple columns the code became quite long. Now, I wanted to try named range reference instead but i cannot make it work. This is my current code:

Dim i As Long
For i = 8 To 500

    'if details is incomplete
    If Range("AA" & i).Value > 0 Then
        If Range("AB" & i).Value = "Error" Or Range("AC" & i).Value = "Error" Or Range("AD" & i).Value = "Error" _
        Or Range("AE" & i).Value = "Error" Or Range("AF" & i).Value = "Error" Or Range("AG" & i).Value = "Error" _
        Or Range("AH" & i).Value = "Error" Or Range("AI" & i).Value = "Error" Or Range("AJ" & i).Value = "Error" _
        Or Range("AK" & i).Value = "Error" Or Range("AL" & i).Value = "Error" Or Range("AM" & i).Value = "Error" _
        Or Range("AN" & i).Value = "Error" Or Range("AO" & i).Value = "Error" Or Range("AP" & i).Value = "Error" _
        Or Range("AQ" & i).Value = "Error" Or Range("AR" & i).Value = "Error" Or Range("AS" & i).Value = "Error" _
        Or Range("AT" & i).Value = "Error" Or Range("AU" & i).Value = "Error" Or Range("AV" & i).Value = "Error" _
        Or Range("AW" & i).Value = "Error" Or Range("AX" & i).Value = "Error" Or Range("AY" & i).Value = "Error" Then
            MsgBox "One of the mandatory field is not provided, please check all cells highlighted in yellow & make sure details is provided."
     End If
Endif

I named range AA = "Validation" & range AB:AY = "Details" how can i declare it and use named range instead of writing each columns one by one?

CodePudding user response：

As @Ike suggests - use the COUNTIF formula. Can be used on the worksheet or within VBA. If you want to return the addresses of each error then Find might be a better route.

Sub Test()
    Dim Result As Long
    Result = Errors(Sheet1.Range("AB8:AY500"))
    If Result > 0 Then
        MsgBox "There are " & Result & " errors in the range."
    End If
End Sub

Public Function Errors(Target As Range) As Long
    Errors = WorksheetFunction.CountIf(Target, "Error")
End Function

CodePudding user response：

Conditional formatting can handle this. I have demonstrated for a smaller range. Feel free to apply it for your required range.

NON VBA

Formula used: =AND($AA8>0,AB8="Error")

VBA

You can use conditonal formatting in VBA as well.

Is this what you are trying? I have commented the code. So let me know if you have any questions.

Option Explicit

Sub Sample()
    Dim i As Long
    Dim ws As Worksheet
    Dim CondTrue As Boolean
    
    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
        '~~> Check if there is even one cell which satisfies our condition
        For i = 8 To 500
            If .Evaluate("=AND(AA" & i & ">0,COUNTIF(AB" & i & ":AY" & i & ",""Error"")>0)") = True Then
                CondTrue = True
                Exit For
            End If
        Next i
        
        '~~> If found then apply conditional formatting
        If CondTrue Then
            With .Range("AB8:AY500")
                .FormatConditions.Delete
                
                .FormatConditions.Add Type:=xlExpression, _
                                      Formula1:="=AND($AA8>0,AB8=""Error"")"
                
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                
                With .FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535
                    .TintAndShade = 0
                End With
                .FormatConditions(1).StopIfTrue = False
            End With
            
            '~~> Show message box
            MsgBox "One of the mandatory field is not provided, please check all cells highlighted in yellow & make sure details is provided."
        Else
            MsgBox "All Good!"
        End If
    End With
End Sub

IN ACTION (VBA)