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
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
Problems with formating text within a cell.
Maciej Fox in Excel on
10 Replies
Copy cell value from different table row
Edgar Soares in Excel on
2 Replies
Which formula to use
Ramon Haagen in Excel on
2 Replies
Automatic coloring sunburst chart
stefan645 in Excel on
2 Replies
function talking to table storage
donquijote in Compute on
0 Replies