Merged cell color issue

[amauer]

I have a macro that changes the color of a merged cell once data has been entered, but it only changes the main cell, but the hidden merged portion of the merged cell ( A12 and A13 are merged, A12 color will change, but not A13). I have the macro set to change active cell once data is entered, so the hidden portion is not changing. My issue is; I have a macro that checks for cells with the incomplete color, so the macro finds the hidden portions of the merged cells and returns an answer that has several incomplete cells.

If I manually change the color, the hidden cells do change, but my macro does not change the hidden portions.

Is there a way to get all merged portions of the cells to change color?

Thanks!

sub for cell (cell click event):

myValue = InputBox("What is the MIX number?")
ActiveCell.Value = myValue
ActiveCell.Interior.ColorIndex = 4

sub for checking cell color (assigned to a button, once the sheet is complete):

For Each cell In myRange
If cell.RowHeight <> 0 Then
If cell.Interior.Color = 15773696 Then
a = a + 1
ElseIf cell.Interior.Color = 65535 Then
y = y + 1

ElseIf cell.Interior.Color = 10498160 Then
x = x + 1

End If

End If

Next cell
Sheets("Validation").Cells(13, 1) = a
Sheets("Validation").Cells(12, 1) = y
Sheets("Validation").Cells(14, 1) = x

 

[mumps]

Does this help?

Sub Test()
    With Range("A12")
        If .MergeCells Then
            .Interior.ColorIndex = 4
        End If
    End With
End Sub

 

[amauer]

Thanks, I will get that a try.

 

[amauer]

I added:

Set myCell = ActiveCell

xx = myCell.Address

With Range(xx)

If .MergeCells Then

.Interior.ColorIndex = 4

End If

End With

End Sub

But when I separate the merged cells, the color is still the original. It appears that "fill" works to color the hidden cell, but not ActiveCell.Interior.ColorIndex = 4. I am going to change ActiveCell.Interior.ColorIndex = 4 to something that will fill the color instead.

Thanks for the suggestion.

 

[mumps]

If merged A12 and A13. I selected A12 to make it the active cell and I ran the following macro:

Sub Test()
    With ActiveCell
        If .MergeCells Then
            .Interior.ColorIndex = 4
        End If
    End With
End Sub

I then unmerged the cells and only A12 remained colored. If you want to remove the color when you unmerge the cells, then try this macro. It will color if they are merged and remove the color if they are not merged.

Sub Test()
    With ActiveCell
        If .MergeCells Then
            .Interior.ColorIndex = 4
        Else
            .Interior.ColorIndex = xlNone
        End If
    End With
End Sub

 

[amauer]

I used this to get the merged part of the cells to get the color change. Worked perfectly. Now when I unmerge the cells (just to check the hidden color), I see the hidden part has also changed colors. Now when I check for any cells that were not completed, I do not get a positive result from the hidden cells.

Sub Rmerge()
Set Rng = ActiveCell
xx = Rng.Address
Range(xx).Select
With Selection.Interior
.Color = 388368
End With
End Sub

 

[mumps]

What do you mean by "hidden cells"? To be honest with you, I don't quite understand what you are trying to do. Could you post a sample of your data. Click this link for help in doing that (Section B): https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
Include a step-by-step detailed explanation referring to specific cells and ranges using a few examples.

 

[amauer]

I have a run sheet that gets used by several people over the day. Once a job has ran, someone will click a cell that will add their Windows ID (or a time or a job number...) and color the cell green to show it was done. After the day is over I run a macro to check that all cells have changed color. The issue I was having is that if a cell was merged with another cell (A12 with A13), A13 was not changing color, it stay the original color. When I ran my end of day validation, the validation was finding A13 was not green and returned a positive result. I would search the large sheet, but could not find any cells that were not green.
code to check that all cells are green

For Each cell In myRange
If cell.RowHeight <> 0 Then
    If cell.Interior.Color = 15773696 Then
        a = a + 1
    ElseIf cell.Interior.Color = 65535 Then
        y = y + 1
        
    ElseIf cell.Interior.Color = 10498160 Then
        x = x + 1
        
    End If
    
End If
    
Next cell
Sheets("Validation").Cells(13, 1) = a
Sheets("Validation").Cells(12, 1) = y
Sheets("Validation").Cells(14, 1) = x

code for cell's click event

myValue = InputBox("What is the MIX number?")
 ActiveCell.Value = myValue
 ActiveCell.Interior.ColorIndex = 4

let me know if you need anything further. I was referring to hidden cells as the part of the cell that is not scene because it is merged with another cell. Cell A13's color was hidden. It appears that I was searching for .color when I should have been searching for .colorindex.

 

[amauer]

I used:

Sub Rmerge()
 Set Rng = ActiveCell
 xx = Rng.Address
 Range(xx).Select
 With Selection.Interior
 .Color = 388368
 End With
 End Sub

instead of

ActiveCell.Interior.ColorIndex = 4

to get it to work they way I wanted.

 

[mumps]

When two or more cells are merged, they shouldn't be hidden. They should all be visible as one larger cell. So if A12 and A13 are merged, they are both visible as one larger cell. See if this helps:

Sub Test()
    myValue = InputBox("What is the MIX number?")
    ActiveCell.Value = myValue
    With ActiveCell
        If .MergeCells Then
            .Interior.ColorIndex = 4
        Else
            .Interior.ColorIndex = 4
        End If
    End With
End Sub