Results 1 to 7 of 7

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

  1. #1
    Seeker Paoloskie's Avatar
    Join Date
    Dec 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Seeker Paoloskie's Avatar
    Join Date
    Dec 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 2016
    Here's the attachement. Thank you.

    Link: https://drive.google.com/file/d/1YNp...ew?usp=sharing

  3. #3
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    122
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    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?

  4. #4
    Seeker Paoloskie's Avatar
    Join Date
    Dec 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 2016
    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

  5. #5
    Seeker jolivanes's Avatar
    Join Date
    May 2020
    Posts
    6
    Articles
    0
    Excel Version
    2007 & 2016
    Attach a normal workbook. No formatting (show all columns and rows)

  6. #6
    Seeker jolivanes's Avatar
    Join Date
    May 2020
    Posts
    6
    Articles
    0
    Excel Version
    2007 & 2016
    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

  7. #7
    Seeker Paoloskie's Avatar
    Join Date
    Dec 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 2016
    Thank you jolivanes. You're a life saver. Bless you mate.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •