takkforalt
New Member
- Joined
- Feb 27, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- 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
----
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: