VBA Code to Preserve Formatting Based on Index Match Formula

[Betnosam]

First tab: 9.17.19
Second tab: Next Download

I'm using index/match to pull whatever is in $O$2:$U$300 in the first tab based on if $A2 matches $A:$A in the first tab. So, in cell O2 of the second tab, IFERROR((INDEX('9.17.19'!O:O,MATCH($A2,'9.17.19'!$A:$A,0)))&"","")
I use a similar formula for columns P through U which is why I didn't make O an absolute value.

There will be highlighting, bolding, itialicizing going on in the first tab in cell range $O$2:U$300 which I want to preserve on the second tab so long as A2 in second tab matches column A in first tab.

I need assistance in the vba code.

 

[DanteAmor]

Hi @★ Betnosam Welcome to the forum!

Try this

Sub Preserve_Formatting()
  Dim sh1 As Worksheet, sh2 As Worksheet, f As Range, i As Long, lr As Long
  Application.ScreenUpdating = False
  Set sh1 = Sheets("9.17.19")
  Set sh2 = Sheets("Next Download")
  lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
  sh2.Range("O2:U" & lr).Clear
  For i = 2 To lr
    Set f = sh1.Range("A:A").Find(sh2.Range("A" & i), , xlValues, xlWhole)
    If Not f Is Nothing Then
      sh1.Range("O" & f.Row & ":U" & f.Row).Copy
      sh2.Range("O" & i).PasteSpecial xlPasteValues
      sh2.Range("O" & i).PasteSpecial xlPasteFormats
    End If
  Next
  Application.CutCopyMode = False
End Sub

 

[JoeMo]

Not sure I understand your request. If you want to preserve your INDEX-MATCH formulas in "Next Download" sheet columns O:U while matching the format of the matched cells in col A of sheet 9.17.19, maybe something like this which is for col O but can easily be expanded to cover the other columns.

Sub Betnosam()
'Example for col O of Next Download sheet
Dim N As Variant, c As Range
Application.ScreenUpdating = False
For Each c In Sheets("Next Download").Range("O:O")
    If Not c.Value = "" Then
        N = [MATCH($A2,'9.17.19'!$A:$A,0)]
        If Not IsError(N) Then
            Sheets("9.17.19").Cells(N, "O").Copy
            c.PasteSpecial xlPasteFormats
        End If
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[Luna17]

I have the same problem. Please help. I can't do conditional formatting because it is just one cell with different text colors.

I want to preserve the text formatting (specific color, bold) of Sheet 1'!$A$2:$G$2 after I used index and match.

=INDEX('Sheet 1'!$A$2:$G$2,MATCH(April!D$10,'Sheet 1'!$A$1:$G$1,0))

This is Sheet 1

This is where I want to preserve the formatting after using Index and Match formula (April tab)

 

[adraam]

I have the same problem. Please help. I can't do conditional formatting because it is just one cell with different text colors.

I want to preserve the text formatting (specific color, bold) of Sheet 1'!$A$2:$G$2 after I used index and match.

=INDEX('Sheet 1'!$A$2:$G$2,MATCH(April!D$10,'Sheet 1'!$A$1:$G$1,0))

This is Sheet 1
View attachment 10176

This is where I want to preserve the formatting after using Index and Match formula (April tab)
View attachment 10177

I am having the same issue, I would like to keep the formatting after I referenced it. Did you find a solution?

 

[Fluff]

Hi & welcome to MrExcel.
You cannot do that with a formula, you will need VBA.
If you are happy to do that, then you will need to start a thread of your giving all relevant details.

 

[adraam]

Thanks, need to give up on that part.
It seems that even having reference e.g. cell = $A$7 will not copy the formatting of the referenced cell.

using VBA, will need to run the script every time I update $A$7, is that correct to get the formatting?

 

[Fluff]

That's correct, yes.