Using IF to hide a ROW via ColorIndex

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
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:

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?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
What problems are you having with the code?

This will hide, or attempt to anyway, every row in the worksheet.
Code:
Rows.Select.EntireRow.Hidden = True
Oh, and it probably won't work anyway with the Select in there.:)

Perhaps something like this.
Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

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

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
Yeah, that is perfect. Thanks Norie and VoG. I think I'm understanding the logic of it now.

For lastCol

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

can this be applied to an instance such as

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

I like this syntéax instead of the "If ... Then ... Else ... End If"
Code:
    For Each Cell In Range("B2:B" & LastRow)
    Cell.EntireRow.Hidden = Cell.Font.ColorIndex = 1
    Next Cell
kind regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top