VBA Toggle off to show/hide text based on font colour

[Melimob]

Hi

I have a table and in column c I have cells which have black font and cells with a blue font.

When the toggle button is pressed I want the cells which are black to remain black font and the cells which are blue to match their interior colour, in other words, they seem to disappear.

The reason for this is they repeat the header row of that section but it looks too much for them to always show however if I'm filtering another column, I need their values to show as the header row for that section won't.

Any advice gratefully received as I've spent hours trying to work this one out

thank you!

 

[Melimob]

Hi - I feel like I'm so close with this...but no cigar!

Any help would be great..

so this works just to change the whole range which I'm looking at to green:

Private Sub ToggleButton1_Click()
Dim rng As Range
Set rng = Range("Table1[Country]")
With rng.Font
    .ColorIndex = IIf(.ColorIndex = 50, 1, 50)
End With
End Sub

and this is how I've tried to adapt it but nothing happens?:

Private Sub ToggleButton1_Click()
Dim rng As Range
Set rng = Range("Table1[Country]")
With rng.Font
    If .ColorIndex <> 1 Then
    .Interior.ColorIndex
    End If
End With
End Sub

 

[Melimob]

Hi - I feel like I'm so close with this...but no cigar!

Any help would be great..

so this works just to change the whole range which I'm looking at to green:

Private Sub ToggleButton1_Click()
Dim rng As Range
Set rng = Range("Table1[Country]")
With rng.Font
    .ColorIndex = IIf(.ColorIndex = 50, 1, 50)
End With
End Sub

and this is how I've tried to adapt it but nothing happens?:

Private Sub ToggleButton1_Click()
Dim rng As Range
Set rng = Range("Table1[Country]")
With rng.Font
    If .ColorIndex <> 1 Then
    .Interior.ColorIndex
    End If
End With
End Sub

OK so tried another approach:

Private Sub ToggleButton2_Click()
If ToggleButton2.Value = True Then
 'This area contains the things you want to happen
'when the toggle button is not depressed
UnhideFont
Else
'This area contains the things you want to happen
'when the toggle button is depressed
 HideFont
  End If


End Sub

and by Subs for Hide/unhide are

Private Sub HideFont()
    Dim cell As Variant
    Dim rng As Range
    Set rng = Range("Table1[Country]")
    For Each cell In rng
        cell.Font.Color = cell.Interior.Color
    Next cell
End Sub


Sub UnhideFont()
    Dim cell As Variant
    Dim rng As Range
    Set rng = Range("Table1[Country]")
    For Each cell In rng
        cell.Font.Color = 1
    Next cell
End Sub

Hide: changes the whole column to interior color but I only want to do for the cells where font is NOT black
Unhide: changes the whole column to black - again, if I can get 'hide to work' I only want to change the ones which were previously 'interior color' to say color 25 (so that hide won't see them as black)?

tearing my hair out here but any advice would be wonderful please!

 

[Melimob]

I thought this would work but no

Private Sub HideFont()
    Dim cell As Variant
    Dim rng As Range
    Set rng = Range("Table1[Country]")
    For Each cell In rng
       If cell.Font.Color <> 1 Then
       cell.Font.Color = cell.Interior.Color
       End If
    Next cell
End Sub

Just hides all

 

[Melimob]

SOLVED

I played around with it and managed to figure it out so posting the final code here in case it helps anyone else:

Private Sub HideFont()
    Dim cell As Variant
    Dim rng As Range
    Set rng = Range("Table1[Country]")
    For Each cell In rng
       If cell.Font.Color <> vbBlack Then
       cell.Font.Color = cell.Interior.Color
       End If
    Next cell
End Sub


Sub UnhideFont()
    Dim cell As Variant
    Dim rng As Range
    Set rng = Range("Table1[Country]")
    For Each cell In rng
        If cell.Font.Color <> vbBlack Then
        cell.Font.Color = 50
       End If
    Next cell
End Sub

then my toggle is:

Private Sub ToggleButton2_Click()
If ToggleButton2.Value = True Then
 'This area contains the things you want to happen
'when the toggle button is not depressed
UnhideFont
Else
'This area contains the things you want to happen
'when the toggle button is depressed
 HideFont
  End If


End Sub

thank you all!