Hide rows with conditions macro

[pincivma]

Hi again

With the help of this forum, I created a macro that hides rows. What I want the macro to do is to hide rows from columns 7 to 10 whose cells have a white background (no color) and at the same time the font is black
Below is my code. I do not get an error but nothing happens when I execute the macro. The rows are not hidden at all. What did I do wrong in the macro below?. There is something wrong. The problem is with the the line in red below

If Cells(x, 7).Interior.TintAndShade = 0 And Cells(x, 7).Font.ColorIndex = Automatic And Cells(x, 8).Interior.TintAndShade = 0 And Cells(x, 8).Font.ColorIndex = Automatic _
And Cells(x, 9).Interior.TintAndShade = 0 And Cells(x, 9).Font.ColorIndex = Automatic And Cells(x, 10).Interior.TintAndShade = 0 And Cells(x, 10).Font.ColorIndex = Automatic Then

Cells(x, 7).Interior.TintAndShade = 0 (this code checks to see if the Cell has no color) and Cells(x, 7).Font.ColorIndex = Automatic (this code checks to see if the font is black). But I believe that these 2 lines of code is wrong. So can someone out there give me the correct code? Thanks

Sub Test1()
Application.ScreenUpdating = False

Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, 6).Select
ActiveCell.FormulaR1C1 = "."
Range("G1").Select
Do
x = ActiveCell.Row
If Cells(x, 7).Interior.TintAndShade = 0 And Cells(x, 7).Font.ColorIndex = Automatic And Cells(x, 8).Interior.TintAndShade = 0 And Cells(x, 8).Font.ColorIndex = Automatic _
And Cells(x, 9).Interior.TintAndShade = 0 And Cells(x, 9).Font.ColorIndex = Automatic And Cells(x, 10).Interior.TintAndShade = 0 And Cells(x, 10).Font.ColorIndex = Automatic Then
Rows(x).Hidden = True
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.FormulaR1C1 = "."
End Sub

 

[jasonb75]

To test for no fill you would need to check the pattern not the colour

If Cells(x, 7).Interior.Pattern = xlNone

For the font, try

Cells(x, 7).Font.Color = 0

Automatic is not a valid keyword in your code, it is an undeclared and undefined variable (it has no meaning). The correct keyword is xlAutomatic, the default value of which is not comparable to the font colour.

 

[pincivma]

Thanks for the code, Jason. I will give it a try and see what happens

 

[pincivma]

I tested out your code and it did not work properly. The rows that I want deleted do have numbers or text in them but the cells background has no color. It is white. Your code skips these rows and deletes rows that have no number or text in them. Your code If Cells(x, 7).Interior.Pattern = xlNone. This code deletes rows that have nothing in them.
Can you revise your code??

 

[jasonb75]

hide rows from columns 7 to 10 whose cells have a white background (no color) and at the same time the font is black

I based my suggestion on what you said above, where I read (no color) as meaning the default 'No Fill' white background, which is not the same as a cell which has been changed to solid white (White colour) to hide the gridlines.

If you start with a new, blank sheet. The default cell background is Pattern =xlNone. If you then change the fill to white (covering the grid lines) then in becomes Pattern = xlSolid
In both cases the colour is the same Interior.Color = 16777215 meaning that testing for colour alone may not always give the desired result.

Note also that I've used Color, not ColorIndex! These are completely different things. If you click the dropdown by the cell colour paint can icon, you will see a colour chart / grid. ColorIndex = 0 refers to all of the colours on the top row of that chart / grid. Testing by ColorIndex would also mean needing to test the ThemeColor setting in order to identify the correct column.

Regarding the font, you said and at the same time the font is black
Font is also a format setting, an empty cell has a black font by default. If you want to specifically look at cells with content, then you need to look at the content, not the font!

Also, in post 1 you said you want to Hide the rows, in post 4 you mention 'Deletes rows', so which is correct?

I will help you to solve this, but some clarity is needed first on the actaul requirement.

 

[pincivma]

Sorry for the confusion. I want the rows hidden not deleted. Some Columns 7 to 10 have conditional formatting whose cells have a white background. These Cells have back font (Either text or numbers). If all columns 7 to 10 in say row 1 have white background and black font either black text or black numbers then that row is hidden. If say row 2 has a Cell with a colored background in any column from 7 to 10 then that row is not hidden. Other columns in row two can have white background with either numbers or text. As long as row two has a minimum of one colored cell then the row is not hidden. So here are the conditions:

If all columns (7 to 10) have white background and black font (black numbers or black text) then that row is hidden.
If any cell in columns 7 to 10 has a different color other than white, then that row in not hidden
If any cell in columns 7 to 10 has a different color text or number other than black, then that row in not hidden.

I hope that this clarifies things.

 

[jasonb75]

Cell / font colour applied by conditional formatting is not the same as standerd formatting.

If the original cell has the default settings of white (NoFill) and black font, then it will still be seen as that even if the conditional fomatting has changed them to yellow and red.

In such cases, you're better off using the conditional formatting criteria to hide the rows rather than the colours.

 

[pincivma]

One more thing about the black font. I clicked on Format Cells > Font > Color > Theme color. The first black square is selected. I'm not sure if this helps you or not.

 

[pincivma]

OK I will use conditional formatting to hide rows Thanks for all your help. You have been very informative.

 

[jasonb75]

One more thing about the black font. I clicked on Format Cells > Font > Color > Theme color. The first black square is selected. I'm not sure if this helps you or not.

That is the default colour though, if you have conditional formatting then the colour selected is not comparable.
Take a concrete wall (probably grey) and paint it white. Format Cells > Font > Color > Theme color is the colour of the concrete (grey), conditional formatting is the colour of the paint (white).
No matter how many coats of paint you apply, the concrete behind it is still grey.

The code is always going to see the colour of the concrete, not the colour of the paint.

OK I will use conditional formatting to hide rows

You can't use conditional formatting to hide rows, you need to use the same, or similar criteria in vba.

Without a bit more detail, such as a data sample and the rules of the conditional formatting, there is not much more that I can suggest.