Consulting

Results 1 to 17 of 17

Thread: VBA: color given data with given color.

  1. #1

    VBA: color given data with given color.

    VBA: color given data with given color.

    Sheet named "USA" has multiple columns. In each columns I have data ( with or with out gap strings or numbers). I have colored each column data with a different color. Say "Data in Column A is blue", "Data in Column C is Red" , "Data in Column B data is Green"........ upto any column. Any column data will not match with one another.

    Now in multiple sheets if there is any data appears anywhere and if any string or number matched with data ( with or with out gap strings or numbers) from Sheet named "USA" then color the appearing string or number with the given color.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Some Text
    3

    This is some text
    1234
    Like this?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    wow
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Click the button in the attached. Case insensitive.
    Macro code is:
    Sub blah()
    For Each cll In Sheets("USA").UsedRange.Cells
      myPhrase = Trim(cll.Value)
      phraseLength = Len(myPhrase)
      If phraseLength > 0 Then
        clr = cll.Font.Color
        For Each sht In ThisWorkbook.Sheets
          If sht.Name <> "USA" Then
            For Each celle In sht.UsedRange
              x = InStr(1, celle.Value, myPhrase, vbTextCompare)
              If x > 0 Then celle.Characters(Start:=x, Length:=phraseLength).Font.Color = clr
            Next celle
          End If
        Next sht
      End If
    Next cll
    End Sub
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    Thank you PASCAL.
    And if I also want to see the partially matching with regex.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Supply the regex code and examples of what you're after. Say, in a workbook.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Simply say I want to match upto 3 letters only and want to color with below plan. [Gaps in-between, before and after is considered]

    Multi ply = Multi ply
    Multiply = Multi pl
    Multiply = Multi p
    Multiply = Multi
    Multiply = Mult
    Multiply = Mul

  9. #9
    I request another VBA code to consider color up to 4 charterer match.

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Supply the regex code and examples of what you're after. For example a few cells showing the text before highlighting, some cells showing what you're looking for, and some clls where you've manually highlighted the corresponding parts - in a workbook.
    I can't make any sense out of your msg#8.
    The code I supplied will allow you to consider any number of charterers match.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    Here it is..
    Attached Files Attached Files

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    try:
    Sub blah2()
    For Each cll In Sheets("USA").UsedRange.Cells
      myPhrase = Trim(cll.Value)
      phraseLength = Len(myPhrase)
      For j = phraseLength To 4 Step -1
        For i = 1 To phraseLength - 3
          myNewPhrase = Trim(Mid(myPhrase, i, j))
          myNewPhraselength = Len(myNewPhrase)
          If myNewPhraselength > 0 Then
            clr = cll.Font.Color
            For Each sht In ThisWorkbook.Sheets
              If sht.Name <> "USA" Then
                For Each celle In sht.UsedRange
                  x = InStr(1, celle.Value, myNewPhrase, vbTextCompare)
                  If x > 0 Then
                    celle.Characters(Start:=x, Length:=myNewPhraselength).Font.Color = clr
                    Do
                      x = InStr(x + 1, celle.Value, myNewPhrase, vbTextCompare)
                      If x > 0 Then celle.Characters(Start:=x, Length:=myNewPhraselength).Font.Color = clr
                    Loop Until x = 0
                  End If
                Next celle
              End If
            Next sht
          End If
        Next i
      Next j
    Next cll
    End Sub
    It uses a brute force technique so be prepared to wait (15 secs here).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    Thank you.....
    Then I want to extract them in Canada sheet...please have a look.

  14. #14
    Can we get this as well as in the excel file ?
    Attached Files Attached Files

  15. #15
    Hello pascal one last issue. I have attached a file in 2nd last post.

  16. #16
    Hello I am looking for a VBA code for last attachment p45cal

  17. #17
    Can I get all words or shared words in cells that are in color (partial/full) in cells of a new sheet?

Tags for this Thread

Posting Permissions

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