Conditional Formatting Vlookup Results

takkforalt

New Member
Joined
Feb 27, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi, New here so please don't shoot me down!

I'm wondering if there's a way either with or without VBA to conditional format the results in a cell that has performed a vlookup?

The context is that I want to report in that cell, the ingredients of a recipe based on a selected product code, however I'd like that recipe to have in bold any allergen concerns. This allergen list can be kept in a range table, but it needs to be specifically just the allergen word that is "bolded" and not the entire string/result.

I've got some code (inherited) that works for a cell that has values in it rather than the results of a vlookup, but i need to preserve the formula from the lookup for easy refresh of the report/printout.

Any advice very much welcomed!

Thanks


----

VBA Code:
Sub MakeBoldItalic()

Set iCell = ActiveCell

For Each cl In Worksheets("Allergens").Range("A2:A59")
  
        searchText = cl.Value
      
        If searchText <> "" Then
          
            totalLen = Len(searchText)
            startPos = InStr(iCell, searchText)
            testPos = 0
  
            Do While startPos > testPos
              
                With iCell.Characters(startPos, totalLen).Font
                   .FontStyle = "Bold"
                End With
      
                testPos = startPos + totalLen
                startPos = InStr(testPos, iCell, searchText, vbTextCompare)
        
            Loop
        End If
    Next cl
  
    searchCoconut = "Coconut Milk"
  
    totalLen = Len(searchCoconut)
    startPos = InStr(iCell, searchCoconut)
    testPos = 0
  
    Do While startPos > testPos
              
        With iCell.Characters(startPos, totalLen).Font
            .FontStyle = "Regular"
            .Underline = xlUnderlineStyleNone
        End With
      
        testPos = startPos + totalLen
        startPos = InStr(testPos, iCell, searchCoconut, vbTextCompare)
        
    Loop
End Sub
 
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi & welcome to MrExcel.
You cannot format part of a cell if it contains a formula.
 
Upvote 0

Forum statistics

Threads
1,223,922
Messages
6,175,384
Members
452,639
Latest member
RMH2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top