Custom format as ;;; to make text invisible but doesn't work with filter?

[Melimob]

Hi

I thought I had found an amazing solution to an issue whereby a column I wanted to filter had blank rows in between.
I autofilled the rows with the value above but as I didn't want the text to show no matter what the cell colour was changed to, I formatted the cells using custom ;;;

this worked great BUT...

Now that I'm filtering, it doesn't find those formatted cells? Only if I use 'contains' as the filter and then type the text but I don't want the user to have to do this each time?

Any advice gratefully received!

thank you

 

[Computerman]

Melimob,
You can do this by making the font color of the text in the cell equal to the interior colorindex of the cell like this:

Sheet1.Cells(1, 1).Font.ColorIndex = Sheet1.Cells(1, 1).Interior.ColorIndex

You can place this code in the click event of a command button or on the Worksheet SelectionChange event

HTH,
Computerman

 

[Melimob]

Melimob,
You can do this by making the font color of the text in the cell equal to the interior colorindex of the cell like this:

Sheet1.Cells(1, 1).Font.ColorIndex = Sheet1.Cells(1, 1).Interior.ColorIndex

You can place this code in the click event of a command button or on the Worksheet SelectionChange event

HTH,
Computerman

thank you so much HTH.. am trying it now.. however is there a way I can toggle this on/off.

I.e. show as darkblue font (or whatever colour) or change to match interior using the click of command?

thank you again!

 

[Melimob]

thank you so much HTH.. am trying it now.. however is there a way I can toggle this on/off.

I.e. show as darkblue font (or whatever colour) or change to match interior using the click of command?

thank you again!

actually, I tried below code adapting it and nothing happened?

My column is row C but thinking about it, some of the rows need to have the colour showing and some need to be hidden.
I've converted it to a table but so it doesn't look so busy it has a 'header row' as the country and then repeated to the next so I can filter but I want the repeated rows font to be hidden?

this is the code I tried. My data is on worksheet named "Project Timelines New" and it's a table, column C starting from row6 as header/8 as data

Private Sub FontClr_Click()
Sheet13.Cells(3, 3).Font.ColorIndex = Sheet13.Cells(3, 3).Interior.ColorIndex
End Sub

Many thanks

 

[Computerman]

Melimob,
First HTH means Hope That Helps. My name is Computerman.
Second we write an IF Then statement like this:

If Sheet1.Cells(1, 1).Font.ColorIndex = Sheet1.Cells(1, 1).Interior.ColorIndex Then
    Sheet1.Cells(1, 1).Font.ColorIndex = 55	‘Dark Blue
Else
    Sheet1.Cells(1, 1).Font.ColorIndex = Sheet1.Cells(1, 1).Interior.ColorIndex
End If

Keep in mind though that if the text is black, the Dark Blue text will not show up very well.

HTH,
Computerman

 

[Melimob]

Melimob,
First HTH means Hope That Helps. My name is Computerman.
Second we write an IF Then statement like this:

If Sheet1.Cells(1, 1).Font.ColorIndex = Sheet1.Cells(1, 1).Interior.ColorIndex Then
    Sheet1.Cells(1, 1).Font.ColorIndex = 55    ‘Dark Blue
Else
    Sheet1.Cells(1, 1).Font.ColorIndex = Sheet1.Cells(1, 1).Interior.ColorIndex
End If

Keep in mind though that if the text is black, the Dark Blue text will not show up very well.

HTH,
Computerman

sorry Computerman... learning slang as well as excel

May I just ask, 1, 1 = row 1 cell 1 correct?
so If I need to do it for the whole column (or column of a table) how would I adapt this please?

and if I'm placing in a click function, by clicking the command would it toggle on and off?

sorry for all the questions and thank you again Computerman!

 

[Computerman]

Melimob,
Questions are how we learn. So ask away. There are two ways to reference Cells in a spreadsheet. One way is to use Range like this:

Sheet.range(“A1”). Value

In this case the first character denotes the column and the second character denotes the row.
Another way to reference a cell is like this:

Sheet.cells(1,1).value

In this case the first character denotes the row and the second character denotes the column, the opposite of the Range method.
Now to affect an entire column depends on a few things, like is the column always in the same place and is the same size, or does it move or grow and shrink. But, basically you would wrap the whole IF THEN code inside a FOR NEXT loop.
And by placing the IF THEN function in the click event of a command button, then you are correct in that each click would toggle the event on and off.
One thing that might help, and this is what I do, is to create a Test workbook. That way you can test different code without it affecting your live workbooks. Once I get the code to do what I want in the Test workbook I then move the code over to my live Workbook
HTH,
Computerman

 

[Melimob]

Melimob,
Questions are how we learn. So ask away. There are two ways to reference Cells in a spreadsheet. One way is to use Range like this:

Sheet.range(“A1”). Value

In this case the first character denotes the column and the second character denotes the row.
Another way to reference a cell is like this:

Sheet.cells(1,1).value

In this case the first character denotes the row and the second character denotes the column, the opposite of the Range method.
Now to affect an entire column depends on a few things, like is the column always in the same place and is the same size, or does it move or grow and shrink. But, basically you would wrap the whole IF THEN code inside a FOR NEXT loop.
And by placing the IF THEN function in the click event of a command button, then you are correct in that each click would toggle the event on and off.
One thing that might help, and this is what I do, is to create a Test workbook. That way you can test different code without it affecting your live workbooks. Once I get the code to do what I want in the Test workbook I then move the code over to my live Workbook
HTH,
Computerman

thank you again for being so patient with me.

So the column will always be column c so I guess it's 3 but the rows will shrink/expand. This is why I put it in a table.

It will always start however in row 8 (as databodyrange not header).

I tested the original code you sent and put value in cell a1 (so 1,1) should have worked but it didn't seem to change anything?

Thinking about it now, I would like to:
for column c in table
if cell is black, leave as black
otherwise, change font to match interior
however upon click, change all to black (toggle this off/on)
is this possible?

 

[Melimob]

thank you again for being so patient with me.

So the column will always be column c so I guess it's 3 but the rows will shrink/expand. This is why I put it in a table.

It will always start however in row 8 (as databodyrange not header).

I tested the original code you sent and put value in cell a1 (so 1,1) should have worked but it didn't seem to change anything?

Thinking about it now, I would like to:
for column c in table
if cell is black, leave as black
otherwise, change font to match interior
however upon click, change all to black (toggle this off/on)
is this possible?

ok this is what I tried under command but it's coming up with object error and still doesn't take care of toggling on/off?

Private Sub FontClr_Click()
With Sheet13.Range("Table1[Country]")
    If Font.ColorIndex = vbBlack Then
       Font.ColorIndex = vbBlack
    Else
       Font.ColorIndex = Interior.ColorIndex
End If
End With
End Sub

 

[Melimob]

ok this is what I tried under command but it's coming up with object error and still doesn't take care of toggling on/off?

Private Sub FontClr_Click()
With Sheet13.Range("Table1[Country]")
    If Font.ColorIndex = vbBlack Then
       Font.ColorIndex = vbBlack
    Else
       Font.ColorIndex = Interior.ColorIndex
End If
End With
End Sub

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