Cell Background Colour Not Being Identified

[Ark68]

Please consider this code:

 Sub singlesegment()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With ws_form
        With segtar
            If .Interior.Color = ccgreen Then .Cells.Interior.Color = xlNone
            If .Interior.Color = xlNone Then .Cells.Interior.Color = ccgreen
        End With
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

'setgar' is a publicly declared Range variable equal to the value of target acquired in another worksheet's 'SelectionChange' event. With this code, when that cell is selected, I wish to change it's fill colour based on it's state when selected. If the cell interior is green, it clears the cell of any colour. If it's clear of colour, it sets it green. (ccgreen is a custom colour)

However, it's not working. When I step through the code, it fails to identify the colour of the cell interior, so no actions are taken.

Where is my error?

 

[Marius44]

Hello
I think it's better to set the IF like this:

With ws_form
        With segtar
            If .Interior.Color = ccgreen Then
                .Cells.Interior.Color = xlNone
            ElseIf .Interior.Color = xlNone Then
                .Cells.Interior.Color = ccgreen
            End If
        End With
    End With

Careful. is it setgar or segtar?
Hello,
Mario

 

[Micron]

Try putting a break point on
With segtar line

Then in the immediate window, test the existing colour property
?Segtar.Interior.Color

Also could test if it's ever xlNone in the same fashion

 

[Fluff]

It should be

If .Interior.ColorIndex = xlNone Then .Cells.Interior.Color = ccgreen

but this will always make the cell green, so you need to use If ElseIf

 

[Jaafar Tribak]

Range("a1").Interior.Color = xlNone
MsgBox Range("a1").Interior.Color = xlNone  ' returns FALSE !
MsgBox Range("a1").Interior.Color = vbWhite  ' returns TRUE !

In my excel 2016, xlNone sets the interior color to White. If that's also the case with you, I would check for vbWhite (If your default cell color is white)

 

[Jaafar Tribak]

In fact, xlNone is for ColorIndex not for the Color Property.
So, I think you should be using :

If .Interior.Color = ccgreen Then .Cells.Interior.ColorIndex = xlNone
If .Interior.ColorIndex = xlNone Then .Cells.Interior.Color = ccgreen

And as Fluff said, you need to use If ElseIf.

EDIT: Sorry, I didn't read Fulff's answer properly. He already gave you the correct answer My apologies.

 

[Ark68]

Thank you all for your support. I"ve made the appropriate changes, yet I continue to have the issue.

Sub singlesegment()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With ws_form
        .Unprotect
        With segtar
stop 'to check interior.color value
            If .Interior.Color = ccgreen Then
                .Interior.ColorIndex = xlNone
                .Font.Color = RGB(0, 32, 96)
                cntsel = cntsel - 1
            ElseIf .Interior.ColorIndex = xlNone Then
                .Interior.Color = ccgreen
                .Font.Color = RGB(216, 216, 216)
                cntsel = cntsel + 1
            End If
        End With
        .Protect
    End With
    RemoveCellSelectionBox
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The code still fails to identify with the cell interior colour.

If I hover over 'ccgreen' is displays a value of 32768. When I test for the interior colour of segtar in the immediate window, I get a value of 0. The address value of segtar ($H$18:$I$18) is that of the one selected, and is indeed green (as filled by code with a value = ccgreen).

Yes, I broke a cardinal rule of merging cells (H&I) just to provide the interaction functionality of the form. I hope this isn't the cause as I'd hate to lose that functionality of increased area to click. I would find it odd that the clicking isn't affected by merging cells, nor is filling it.

Thoughts?

 

[Fluff]

That code works for me & toggles between green & no fill quite happily.

 

[Ark68]

Hmmm, thanks Fluff. Certainly, if you have it working there is no doubt in my mind that something else must be going on in my project messing it up of me. It's a pretty simple linear program so don't know what background actions had been written to keep this from happening. I'm stumped.

 

[Ark68]

- Ran out of time to edit post 9 -
Ok, so it does work for me as well under a certain scenario.
Consider how my application works. I have a series of rows representing a segment of a greater collection. So for instance, collection is in row 8. Rows 9-18 are segments of that collection.
For each row, the user can click on a cell - H10 through H18. When the user clicks on any of the segment selections (H9-=18), the 'singlesegment' routine kicks in. As Fluff has confirmed, as I have, works to toggle between on (green) and off (no colour). Works as indended.

However,

The user can also simple click on the collection select cell (H8). Doing this toggles all segement selection cells under it (H9-H18) between on (green) and off (no coloure). With all the segment selection cells in one of two states on/off) collectively, if the user clicks on one of the segement select cells (H9-H18), the indea (using the code I provided above) is just that segment cell will toggle. This is NOT happening with the code above.

Here is the code that on selection of the collection select cell (H8) toggles the segment selection cells (H9:H18). I think something with this code might be interfering with the users ability to toggle individual cells using the 'singlesegment' code after the collection was selected.

Sub allsegments()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim ccnt As Integer
    Dim cl As Integer
    Dim lastrow As Integer
    'Stop
    With ws_form
        .Unprotect
        If .Range("H8").Interior.ColorIndex = xlNone Then
            .Range("H8").Interior.Color = ccgreen
            .Range("H8:H" & vcnt + 8).Interior.Color = ccgreen
            .Range("H8:H" & vcnt + 8).Font.Color = RGB(217, 217, 217)
            cntsel = cntsel + vcnt
        Else
            .Range("H8").Interior.Color = ccgreen
            .Range("H8:H" & vcnt + 8).Interior.ColorIndex = xlNone
            .Range("H8:H" & vcnt + 8).Font.Color = RGB(0, 32, 96)
            cntsel = cntsel - vcnt
        End If
        .Protect
    End With
    'temp
    lastrow = Application.WorksheetFunction.Match("end", ws_form.Columns(8), 0) - 1 'ws_form.Cells(ws_form.Rows.Count, "H").End(xlUp).Row
    ccnt = 0
    For cl = 8 To lastrow - 1 'to exclude end
        If ws_form.Range("H" & cl).Interior.Color = ccgreen Then ccnt = ccnt + 1
    Next cl
    RemoveCellSelectionBox
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub