Private Sub CommandButton1_Click()
Dim ColorCount As Integer
Dim MyArray
For Z = 1 To ActiveSheet.UsedRange.Rows.Count
  ReDim MyArray(1 To 5)
  ColorCount = 0
  ' 記錄顏色
  For M = 5 To 1 Step -1
    If Cells(Z, M).Interior.Color <> 16777215 Then
      ColorCount = ColorCount + 1
      MyArray(ColorCount) = Cells(Z, M).Interior.Color
    End If
  Next M
  ' 清除顏色
  Range("A" & Z & ":E" & Z).Interior.Color = xlNone
  ' 寫入上面記錄的顏色
  For K = 1 To ColorCount
    Cells(Z, 5 - K + 1).Interior.Color = MyArray(K)
  Next K
Next Z
End Sub

笨一點的做法

dim col_arr
For i = 1 To [e1048576].End(xlUp).Row
    ReDim col_arr(1 To 5)
    For y = 1 To 5
        If Cells(i, y).Interior.Color <> 16777215 Then
            col_arr(y) = Cells(i, y).Interior.Color
        End If
    Next
    Rows(i).Interior.Pattern = xlNone
    row_col = WorksheetFunction.Trim(Join(col_arr))
    x = UBound(Split(row_col, " "))
    If row_col <> "" Then
        For y = 5 To 1 Step -1
            Cells(i, y).Interior.Color = Split(row_col, " ")(x)
            x = x - 1
            If x = -1 Then Exit For
        Next
    End If
Next