Forum Discussion

JoelR1860's avatar
JoelR1860
Copper Contributor
Aug 18, 2024

merging and unmerging with color

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 

  • 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.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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.

Resources