Home

Lookup_concat function that keeps color format of original text

%3CLINGO-SUB%20id%3D%22lingo-sub-863371%22%20slang%3D%22en-US%22%3ELookup_concat%20function%20that%20keeps%20color%20format%20of%20original%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863371%22%20slang%3D%22en-US%22%3E%3CP%3EI%E2%80%99m%20working%20in%20Excel%202013%2C%20so%20I%20don%E2%80%99t%20have%20all%20the%20newer%20bells%20%26amp%3B%20whistles.%20I%20have%20two%20worksheets%2C%20one%20that%20lists%20inspection%20data%20and%20a%20second%20yearly%20calendar%20that%20has%20a%20Lookup_concat%20function%20where%20if%20the%20date%20in%20second%20worksheet%20matches%20the%20inspection%20row%20date%2C%20another%20column%20from%20the%20inspection%20data%20is%20returned.%20As%20all%20rows%20in%20the%20range%20are%20reviewed%2C%20additional%20matches%20are%20added%20to%20the%20result%20giving%20a%20result%20that%20shows%20all%20inspections%20due%20on%20a%20particular%20day%20in%20the%20calendar.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20function%20works%20as%20expected.%20The%20results%20are%20all%20in%20black%20font%20though.%20What%20I%20need%20though%20is%20to%20keep%20the%20conditional%20formatting%20on%20the%20returned%20value%20in%20the%20concatenated%20value%20to%20show%20the%20color%20of%20the%20returned%20value.%20For%20example%2C%20if%20the%20Inspection%20Type%20(column%20C)%20is%20%E2%80%9CPed%E2%80%9D%20then%20the%20ID_Location%20(Column%20F)%20changes%20to%20green%20in%20the%20first%20worksheet.%20In%20the%20YearlyView%20worksheet%2C%20I%E2%80%99d%20like%20all%20inspections%20of%20type%20%E2%80%9CPed%E2%80%9D%20to%20show%20green%20as%20well%2C%20not%20the%20current%20black%20font.%20All%20of%20the%20inspection%20types%20are%20a%20different%20color%20and%20should%20show%20that%20way%20on%20the%20YearlyView.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20similar%20code%20that%20concatenates%20and%20leaves%20the%20color%20formatting%20but%20I%E2%80%99m%20not%20sure%20how%20to%20incorporate%20it%20into%20my%20existing%20function%20that%20matches%20the%20date%20column.%20I%E2%80%99m%20having%20issues%20defining%20constants%20and%20determining%20where%20to%20add%20the%20loop%20to%20look%20at%20the%20formatting%20of%20each%20character.%20I%E2%80%99ve%20included%20this%20other%20found%20code%20in%20my%20module%20even%20though%20it's%20not%20called%20%E2%80%93%20ConcatwithFormat()%20and%20concatenate_cells_formats(cell%20as%20Range%2C%20source%20as%20Range).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%E2%80%99s%20been%20years%20since%20I%E2%80%99ve%20worked%20with%20VBA%20and%20it%20may%20be%20a%20long%20time%20before%20my%20version%20is%20upgraded.%20Thanks%20in%20advance%20for%20any%20help%20you%20can%20offer!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-863371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
page8888
Regular Visitor

I’m working in Excel 2013, so I don’t have all the newer bells & whistles. I have two worksheets, one that lists inspection data and a second yearly calendar that has a Lookup_concat function where if the date in second worksheet matches the inspection row date, another column from the inspection data is returned. As all rows in the range are reviewed, additional matches are added to the result giving a result that shows all inspections due on a particular day in the calendar.

 

This function works as expected. The results are all in black font though. What I need though is to keep the conditional formatting on the returned value in the concatenated value to show the color of the returned value. For example, if the Inspection Type (column C) is “Ped” then the ID_Location (Column F) changes to green in the first worksheet. In the YearlyView worksheet, I’d like all inspections of type “Ped” to show green as well, not the current black font. All of the inspection types are a different color and should show that way on the YearlyView.

 

I found similar code that concatenates and leaves the color formatting but I’m not sure how to incorporate it into my existing function that matches the date column. I’m having issues defining constants and determining where to add the loop to look at the formatting of each character. I’ve included this other found code in my module even though it's not called – ConcatwithFormat() and concatenate_cells_formats(cell as Range, source as Range).

 

It’s been years since I’ve worked with VBA and it may be a long time before my version is upgraded. Thanks in advance for any help you can offer!

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
34 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies