merging and unmerging with color

[JoelR1860]

I have a spreadsheet looking like a calendar. I have Month, Day of week and day of month horizonal. The background is white with boarders around it. I have cabins number listed vertically. I would like to be able to Merge cells and add a name, when entered the background will have a color. When I delete the merge the background will return to whit and the boarders will reappear. I have tried using conditional formatting with not success.

I'm using windows 10 Pro

Excel Version 2407 

[#2 · Accepted solution]

@JoelR1860 

Without VBA, Excel's native capabilities are somewhat limited in terms of automating all the functionality you want. This is something that cannot be achieved using standard Excel features like conditional formatting alone or without using VBA.

Here is an example using VBA.

The code is untested, please save your file before using it.

Sub MergeAndColorCells()
    Dim rng As Range
    Dim color As Long
    
    ' Prompt the user to select a range to merge
    On Error Resume Next
    Set rng = Application.InputBox("Select the range to merge:", Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then Exit Sub
    
    ' Merge the cells
    With rng
        .Merge
        ' Apply a color (e.g., light blue)
        color = RGB(173, 216, 230) ' Change this RGB value to set a different color
        .Interior.Color = color
        ' Center the text
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Color = RGB(0, 0, 0) ' Text color (black)
    End With
End Sub

Sub UnmergeAndClearColor()
    Dim rng As Range
    Dim cell As Range
    
    ' Prompt the user to select a range to unmerge
    On Error Resume Next
    Set rng = Application.InputBox("Select the range to unmerge:", Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then Exit Sub
    
    ' Unmerge the cells and clear the color if the range is empty
    With rng
        .UnMerge
        For Each cell In rng
            If cell.Value = "" Then
                cell.Interior.Color = RGB(255, 255, 255) ' Set background to white
                cell.Borders.Color = RGB(0, 0, 0) ' Restore borders (black)
            End If
        Next cell
    End With
End Sub

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.