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))

 

[Peter_SSs]

Is there some VBA that will enable this?

Try this but you will have to edit the destination cell as you didn't say where your existing formula was.

Sub RetrieveValueAndFormat()
  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)
  If Not rwFound Is Nothing And Not colFound Is Nothing Then
    Cells(rwFound.Row, colFound.Column).Copy
    With Range("AY10")    '<- check destination
      .PasteSpecial xlValues
      .PasteSpecial xlFormats
    End With
    Application.CutCopyMode = False
  End If
End Sub

 

[glad_ir]

Hi Peter,

Thank you very much for replying. Should I put this code in the sheet module?

thanks, Iain

 

[Peter_SSs]

Before answering, as it stands this code is not like a formula in that if any of the input cells changes, the results would not automatically update like a formula does. Is that a problem for you?

 

[glad_ir]

Hi Peter,

The text in AY8 and AY9 are populated by a macro which executes when the user clicks a button.......is there a way to get around the issue this causes?

thanks,
Iain

 

[Peter_SSs]

The text in AY8 and AY9 are populated by a macro which executes when the user clicks a button.......is there a way to get around the issue this causes?

Then why not add the code from my macro in to your existing code at the end so that after your macro has entered the values to AY8 and AY9 then it also updates the relevant destination cell?

 

[glad_ir]

Then why not add the code from my macro in to your existing code at the end so that after your macro has entered the values to AY8 and AY9 then it also updates the relevant destination cell?

Then why not add the code from my macro in to your existing code at the end so that after your macro has entered the values to AY8 and AY9 then it also updates the relevant destination cell?

Hi Peter,

Thank you - this works nicely if the cell with the INDEX/MATCH formula in is a single cell. However, my destination is a merged cell range (W9:Y44) and the VBA doesn't seem to like it expressed in this form or as W9. Do you know how I can modify to accept this as the destination please?

Thanks and sorry to be a pain,

Iain

 

[Peter_SSs]

One of the things the code is doing is to copy the formatting from the source cell to the destination cell. Merging or unmerging cells is part of formatting so that is part of the problem here. However, there are many instances where merged cells and vba do not sit well together.

One option would be to unmerge those cells, do the copy/paste special (to W9 only) then re-merge. If the only formatting you are concerned about is the font colour for the cell then perhaps this would suffice, leaving the merged cells as they are?

Sub RetrieveValueAndFormat_v2()
  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)
  If Not rwFound Is Nothing And Not colFound Is Nothing Then
    With Range("W9")
      .Value = Cells(rwFound.Row, colFound.Column).Value
      .Font.Color = Cells(rwFound.Row, colFound.Column).Font.Color
    End With
  End If
End Sub

 

[glad_ir]

One of the things the code is doing is to copy the formatting from the source cell to the destination cell. Merging or unmerging cells is part of formatting so that is part of the problem here. However, there are many instances where merged cells and vba do not sit well together.

One option would be to unmerge those cells, do the copy/paste special (to W9 only) then re-merge. If the only formatting you are concerned about is the font colour for the cell then perhaps this would suffice, leaving the merged cells as they are?

Sub RetrieveValueAndFormat_v2()
  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)
  If Not rwFound Is Nothing And Not colFound Is Nothing Then
    With Range("W9")
      .Value = Cells(rwFound.Row, colFound.Column).Value
      .Font.Color = Cells(rwFound.Row, colFound.Column).Font.Color
    End With
  End If
End Sub

Hi Peter,

Thank you for this.

When I run this code (either in combination with the other code or by itself) it deletes the formula in the merged destination cell. Any thoughts?

Thanks again for your help,
Iain

 

[Peter_SSs]

When I run this code (either in combination with the other code or by itself) it deletes the formula in the merged destination cell. Any thoughts?

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