VBA code to Highlight cell in Red

[SamarthSalunkhe]

Hi All,

I am using below code to find unwanted character in cell, code is working fine but in addition I want code to highlight wrong value cell in red.

Sub Validate_File()

'Variable Declaration
Dim iCnt As Integer
Dim IpData As Range, DataRange As Range
Dim lr As Long

'-----------------------------------------------------------------------------------------------------------------------------------
'Below code will find Unwanted Characters in Account Number Column
        
    lr = Sheet1.Range("F" & Rows.Count).End(xlUp).Row
    
    'Create Object for Selected Range
    Set DataRange = Sheets("Tally Format").Range("F2:F" & lr)
    
    'Runnning for loop to check all available cells
    For Each IpData In DataRange
    For iCnt = 1 To Len(IpData.Value)
        
        If Not Mid(IpData.Value, iCnt, 1) Like "[0-9A-Z]" Then
            MsgBox "Unwanted Character fould in Column", vbExclamation, "Wrong A/c No"
            Exit Sub
        End If
    Next iCnt
    Next IpData
End Sub

Thank you in Advance

 

[Joe4]

Add the line in red below:

Sub Validate_File()

'Variable Declaration
Dim iCnt As Integer
Dim IpData As Range, DataRange As Range
Dim lr As Long

'-----------------------------------------------------------------------------------------------------------------------------------
'Below code will find Unwanted Characters in Account Number Column
        
    lr = Sheet1.Range("F" & Rows.Count).End(xlUp).Row
    
    'Create Object for Selected Range
    Set DataRange = Sheets("Tally Format").Range("F2:F" & lr)
    
    'Runnning for loop to check all available cells
    For Each IpData In DataRange
    For iCnt = 1 To Len(IpData.Value)
        
        If Not Mid(IpData.Value, iCnt, 1) Like "[0-9A-Z]" Then
            IpData.Interior.Color = 255
            MsgBox "Unwanted Character fould in Column", vbExclamation, "Wrong A/c No"
            Exit Sub
        End If
    Next iCnt
    Next IpData
End Sub

 

[SamarthSalunkhe]

Add the line in red below:

Sub Validate_File()

'Variable Declaration
Dim iCnt As Integer
Dim IpData As Range, DataRange As Range
Dim lr As Long

'-----------------------------------------------------------------------------------------------------------------------------------
'Below code will find Unwanted Characters in Account Number Column
       
    lr = Sheet1.Range("F" & Rows.Count).End(xlUp).Row
   
    'Create Object for Selected Range
    Set DataRange = Sheets("Tally Format").Range("F2:F" & lr)
   
    'Runnning for loop to check all available cells
    For Each IpData In DataRange
    For iCnt = 1 To Len(IpData.Value)
       
        If Not Mid(IpData.Value, iCnt, 1) Like "[0-9A-Z]" Then
            IpData.Interior.Color = 255
            MsgBox "Unwanted Character fould in Column", vbExclamation, "Wrong A/c No"
            Exit Sub
        End If
    Next iCnt
    Next IpData
End Sub

Hi @Joe4

Getting below error.

 

[Joe4]

When you hit "Debug", what line of code does it highlight?

 

[SamarthSalunkhe]

When you hit "Debug", what line of code does it highlight?

 

[Joe4]

Are you on the sheet when the code is run?
Is there any sort of protection on the sheet?

Maybe try adding the sheet reference, i.e.

Sheets("Tally Format").IpData.Interior.Color = 255

 

[SamarthSalunkhe]

Are you on the sheet when the code is run?
Is there any sort of protection on the sheet?

Maybe try adding the sheet reference, i.e.

Sheets("Tally Format").IpData.Interior.Color = 255

Yes my file is protected, I have resolved it.

but I found that above code is only highlighting first error cell not all.

 

[Joe4]

but I found that above code is only highlighting first error cell not all.

It is because you have an "Exit Sub" line in your code. That kicks you out of the entire Sub procedure.

If you just want to exit that loop, and not the entire sub, change "Exit Sub" to "Exit For".

 

[SamarthSalunkhe]

It is because you have an "Exit Sub" line in your code. That kicks you out of the entire Sub procedure.

If you just want to exit that loop, and not the entire sub, change "Exit Sub" to "Exit For".

got it, working fine now.

but I am getting massage box after every error, is it possible to code should highlight all error first and then show massage box.

 

[Joe4]

Here is what I would recommend.
- Put a counter in the loop that counts the number of errors it find
- Remove the message box from the loop
- After the completion of both loops, put an IF...THEN that says IF the error counter > 0, then return a MsgBox