Flipping cells horizontally along with their formatting-CodePudding

I am looking for a solution which will flip my cells horizontally in Excel via Virtual Basic along with cells formatting (I need background colour to flip too).

What I mean by flip is this:

I have the code which does the actual flip of cell values, but DOES NOT move backgrounds, so following the example above, after the flip, the sheep will be blue and a cow green, etc... Is it possible to flip backgrounds too?

The code I have is:

Sub Fliphorizontally()
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For i = 1 To UBound(Arr, 1)
    k = UBound(Arr, 2)
    For j = 1 To UBound(Arr, 2) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(i, k)
        Arr(i, k) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

If that makes a difference - I use Mac, not PC

CodePudding user response：

Try something like this:

Sub Tester()

    FlipRange Selection, True  'flip horizontally
    
    FlipRange Selection, False 'flip vertically

End Sub


Sub FlipRange(rng As Range, FlipHoriz As Boolean)
    Dim arr, arrColors, r As Long, c As Long, ubr As Long, ubc As Long
    Dim rOut As Long, cOut As Long
    arr = rng.Value      'read the data
    ubr = UBound(arr, 1)
    ubc = UBound(arr, 2)
    ReDim arrColors(1 To ubr, 1 To ubc)
    For r = 1 To ubr    'read the fill colors
        For c = 1 To ubc
            arrColors(r, c) = rng.Cells(r, c).Interior.Color
        Next c
    Next r
    For r = 1 To ubr
        For c = 1 To ubc
            rOut = IIf(FlipHoriz, r, 1   ubr - r)
            cOut = IIf(FlipHoriz, 1   ubc - c, c)
            With rng.Cells(rOut, cOut)
                .Value = arr(r, c)
                .Interior.Color = arrColors(r, c)
            End With
        Next c
    Next r
End Sub