Highlight cells in any color for the first 5 characters from a column

P

Paoloskie

New member
Joined
Dec 15, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
Good day Excelguru masters,

I have a quandary about highlighting cells in any color that contains the first 5 characters from a column from another sheet.. It's a simple formula but I don't know how to complete. I believe you have shorter code for this. Thank you so much. (Please request attachment as Excelguru is not allowing me to post the attachment)

Paolo
 
I looked at your file but can't figure out what you want to do. Which cells do you want to highlight? Which sheet are they on? What sheet has the column with the five characters? Which column is it?
 
Hi NormS,

Thanks for replying. Sorry to have just replied.

I have not been able to attach the screenshot. I keep on attaching but I don't know why it always failed.

Anways, I want to highlight the column 'Alternate Code' in the sheet PB Import which has the first 5 characters from column 'Style' in sheet Find.

Thanks in advance.

Paolo
 
Attach a normal workbook. No formatting (show all columns and rows)
 
Code: 
Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim c As Range, i As Long
Set sh1 = Worksheets("Find")
Set sh2 = Worksheets("PB Import")
    For Each c In sh2.Range("E2:E" & sh2.Cells(Rows.Count, 5).End(xlUp).Row)
        For i = 2 To sh1.Cells(Rows.Count, 2).End(xlUp).Row
            If Mid(sh1.Cells(i, 2), 3, 5) = Left(c, 5) Then c.Interior.Color = vbRed: Exit For
        Next i
    Next c
End Sub
 
Thank you jolivanes. You're a life saver. Bless you mate.
 
You must log in or register to reply here.
Back
Top