Automatically highlight a cell to match another cell highlighting (Fill Color)

[slam]

I have data that I'm regularly pasting into columns A through H. The data pasted into Column H has red fill in some cells (which has been applied manually), but the amount of text in this column is very long so I have this column hidden. Then in column I, I have a simple LEFT formula to only show the first 13 characters of what's in column H. What I'm looking to do is for any cells that have red fill in the hidden column H, I want to automatically have the same red fill applied in column I. Is this possible? This is in Excel 365.

Thanks

 

[SunnyAlv]

Try with VBA

Sub color()
On Error Resume Next
Dim r As Range

For Each r In Range("H1", Range("H" & Rows.Count).End(xlUp))

    If r.Interior.ColorIndex = 3 Then
        r.Offset(0, 1).Interior.color = vbRed
    End If
Next

End Sub

 

[Peter_SSs]

. The data pasted into Column H has red fill in some cells

Is there any other colour or formatting in column H that you don't want transferred to column I? If not, you could do it all at once with this Worksheet_Change event code rather than cycling through each individual row of your "very long" column. 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.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range
  
  Set Changed = Intersect(Target, Columns("H"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Changed.Copy
    Changed.Offset(, 1).PasteSpecial xlPasteFormats
    Application.EnableEvents = True
    Application.CutCopyMode = False
  End If
End Sub