Using IF to hide a ROW via ColorIndex

[firefiend]

I brought this up on another forum but I think I should have brought it here instead.

I'm having some trouble with this bit of code and can't seem to figure why it doesn't execute properly.

I need the code to check the cells in the B:B Range and if the cell's font.colorindex = 1 then hide the row, otherwise leave the row as is.

here's the code I currently have

Code:

For Each Cell In Range("B2:B" & lastRow)
If Cell.Font.ColorIndex = 1
Then Rows.Select.EntireRow.Hidden = True
Else: Rows.EntireRow.Hidden = False
End If
Next Cell

It seems pretty straight forward. Any Ideas?

Also, can the same principle be applied to columns? And how does the lastRow variable transpose to counting columns?

lastRow = Cells(Rows.Count, "D").End(xlUp).Row

lastColumn = [this is stumping me]

darn my inferior VBA skills.

Anyone willing to offer some help on this?

 

[Norie]

What problems are you having with the code?

This will hide, or attempt to anyway, every row in the worksheet.

Rows.Select.EntireRow.Hidden = True

Oh, and it probably won't work anyway with the Select in there.

Perhaps something like this.

For Each Cell In Range("B2:B" & lastRow) 
If Cell.Font.ColorIndex = 1 Then 
      Cell.EntireRow.Hidden = True 
Else 
       Cell.EntireRow.Hidden = False 
End If 
Next Cell

 

[VoG]

Try

For Each Cell In Range("B2:B" & LastRow)
If Cell.Font.ColorIndex = 1 Then
    Cell.EntireRow.Hidden = True
Else
    Cell.EntireRow.Hidden = False
End If
Next Cell

To find the last column in row 1

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

 

[firefiend]

Yeah, that is perfect. Thanks Norie and VoG. I think I'm understanding the logic of it now.

For lastCol

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

can this be applied to an instance such as

Range("H2:" & lastCol & "2").Select

I'm thinking not. It seems like the code is just generating a number (COUNT) and won't translate into a column letter. Is this correct?

 

[Jonmo1]

No, because RANGE requires a Column LETTER, Lastcol is a NUMBER...

Try using CELLS

Range(Cells(2,"H"),Cells(2, lastcol)).Select

 

[erik.van.geit]

Hi,

I like this syntéax instead of the "If ... Then ... Else ... End If"

    For Each Cell In Range("B2:B" & LastRow)
    Cell.EntireRow.Hidden = Cell.Font.ColorIndex = 1
    Next Cell

kind regards,
Erik

 

[erik.van.geit]

just got another idea, WITHOUT code

Hi,

without code

select cell B1
define a name (menu insert)
ThisColor
formula
=GET.CELL(24,!A1)

when typing in a cell
=ThisColor
you will get the fontcolor of the cell at the left

then you can use autofilter

problem:
this formula does only update when sheet is calculated
you can solve this using a longer formula
=GET.CELL(24,!A1) + NOW()*0

kind regards,
Erik