vba help - validate input file using instr

[Mallesh23]

Hi Team,

Need help to Validate Correct input file.
if cell "A1" Contains Highlighted string in Red Color.

That will be Genuine input file... Macro to proceed else exit.

how to check using instr or Select case here.

Book37
	A
1	ABC and Co.(Accrual) - 17-18
2	PQR Business Consulting LLP
3	XYZ Pvt.Ltd. (Formely Known As DEF Consultancy Services Pvt.Ltd.) W.E.F 11-2-20
Sheet1

Below is snapshot highlighted red color are instr.

Thanks
mg

 

[afonsomira]

Hello,
Check if this code helps you!

Sub FoundColor()
    Dim I As Long, J As Long, lastRow As Long
    
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    For I = 1 To lastRow
        For J = 1 To Len(Cells(I, 1).Value)
            If Cells(I, 1).Characters(Start:=J, Length:=1).Font.color = vbRed And Cells(I, 1).Characters(Start:=J, Length:=1).Font.Bold = True Then
                MsgBox "Red and Bold Character found on range " & Cells(I, 1).Address
                J = Len(Cells(I, 1).Value) + 1
            End If
        Next J
    Next I
End Sub

 

[Mallesh23]

Hi AfonSomira,

Nice code, Thanks for your help, I want to validate cell A1 only. Below is my attempted code which is working
How to achieve same task using Select Case. just for learning purpose.

Dim str_Company As String
str_Company = ws_input.Range("A1").Value
   
   
   If InStr(1, str_Company, "ABC and Co.", vbTextCompare) = 0 Or _
        InStr(1, str_Company, "PQR Business Consulting LLP", vbTextCompare) = 0 Or _
        InStr(1, str_Company, "XYZ Pvt.Ltd.", vbTextCompare) = 0 Then
    msgbox "Correct input file"
    Else
        MsgBox "Incorrect Input File", vbCritical
    Exit Sub
        
   End If

Thanks
mg

 

[afonsomira]

The code for validate only cell A1:

Sub FoundColor()

Dim J As Long

For J = 1 To Len(Cells(1, 1).Value)
    If Cells(1, 1).Characters(Start:=J, Length:=1).Font.color = vbRed And Cells(1, 1).Characters(Start:=J, Length:=1).Font.Bold = True Then
        MsgBox "Red and Bold Character found on range A1"
        J = Len(Cells(1, 1).Value) + 1
    End If
Next J

End Sub

 

[afonsomira]

You want the select case on which code?
This:

Dim str_Company As String
str_Company = ws_input.Range("A1").Value
  
  
   If InStr(1, str_Company, "ABC and Co.", vbTextCompare) = 0 Or _
        InStr(1, str_Company, "PQR Business Consulting LLP", vbTextCompare) = 0 Or _
        InStr(1, str_Company, "XYZ Pvt.Ltd.", vbTextCompare) = 0 Then
    msgbox "Correct input file"
    Else
        MsgBox "Incorrect Input File", vbCritical
    Exit Sub
       
   End If

Or this:

Sub FoundColor()

Dim J As Long

For J = 1 To Len(Cells(1, 1).Value)
    If Cells(1, 1).Characters(Start:=J, Length:=1).Font.color = vbRed And Cells(1, 1).Characters(Start:=J, Length:=1).Font.Bold = True Then
        MsgBox "Red and Bold Character found on range A1"
        J = Len(Cells(1, 1).Value) + 1
    End If
Next J

End Sub

 

[Mallesh23]

Hi afonsomira,

Thanks once again for your help, really nice code,
Actually my requirement is to check cell a1 whether it contains substring. which are highlighted in bold.
not color, but i liked your code, learned something new in it.




Thanks
mg