Retaining source formatting with INDEX/MATCH

[glad_ir]

Hi,

I wonder if somebody could help me with this one please.

I am using the formula below to retrieve entries from a table and it works as intended but I'd also like to retrieve the source text colour (my table contains red, blue and black text but I only get black in the cell containing the INDEX formula). Is there some VBA that will enable this?

Any help much appreciated!

Thanks,
Iain

=INDEX(AX14:BJ27,MATCH(AY8,AW14:AW27,0),MATCH(AY9,AX14:BJ14,0))

 

[glad_ir]

Well, you could implement the following but note that if the formula in the merged cell changes or the ranges that it applies to change (eg rows are inserted or deleted above), this code will not automatically adjust to the new formula or ranges. The issue is that a formula cannot retrieve formatting from the source cell like that.

You could try this Worksheet_Calculate event code. Any time the worksheet recalculates, the code will format your formula merged cell.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by altering something that will cause a recalculation in the worksheet ( eg just re-enter the value in, say, AY8)

Private Sub Worksheet_Calculate()
  Dim rwFound As Range, colFound As Range
 
  Set rwFound = Range("AW14:AW27").Find(What:=Range("AY8").Value, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False)
  Set colFound = Range("AX14:BJ14").Find(What:=Range("AY9").Value, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False)
  Application.ScreenUpdating = False
  If Not rwFound Is Nothing And Not colFound Is Nothing Then Range("W9").Font.Color = Cells(rwFound.Row, colFound.Column).Font.Color
  Application.ScreenUpdating = True
End Sub

Hi Peter,

You are a superstar! This is perfect.

Thank you so much for taking the time to help me with this. I really appreciate it!

cheers, Iain

 

[Peter_SSs]

You're welcome. Glad we could help.