Formatting all cells including hidden ones

[neiljabba]

Hello all


I am trying to format ALL cells within the list, to have gird lines around the cell, including the hidden cells, so that when the filter is removed all cells have been formatted the same.

As you can imagine it works fine for the unhidden cells but leaves blanks around the filtered values the code is

Public Sub testingblanks()

lRow = Cells(65536, 1).End(xlUp).Row


For r = 3 To lRow

If (Worksheets("Sheet1").Rows(r).Hidden = False And (Cells(r, 4) <> "")) Then

Module1.addcol

End
Else

UserForm1.Show
End


End If

Next r

End Sub


Public Sub addcol()

Columns("D").Select
Selection.Insert Shift:=xlToRight

Range("d3:d194").Select

'Do stuff




'obviously for appearance purposes only the follwing formats the column
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With



Cells(1, 1).Select
UserForm1.Show



End Sub


Is this possible?It makes the eerything easier to read thats all.

If it is Im done hurray!!!!!

Cheers

Neil

 

[SkipVought]

Hi,

Here's a brief snippet of a conversation between a Patient (P) and Doctor (D)

P: "Doc, every time I hit my head with a hammer, it hurts like heck!"

d: "Well, stop hitting your head with a hammer!"

ShowAll

FormatCells

Filter ;-)

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[neiljabba]

Thanks Skip but...

The filter is applied before a new column is inserted with the formatting and depends on various other conditions (See Read and Write to User Form Thread you helped me with). Sp this may cause more problems than its worth.


I thinks the head with hammer may be what Im doing Im not going to be the only end user and I was trying to keep them happy but what the heck maybe youre right.


Cheers anyway

Neil

 

[SkipVought]

The filter is applied before a new column is inserted "

You capture the Filter Criteria, ShowAll, Format Cells, Re-Apply Filter Criteria.

Get rid of that hammer! ;-)

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[neiljabba]

Yeah thought of that one Skip as the filter value comes from an MSOCommandBar drop down, I made it a Public variable anyway, although its not consistent in its filter behaviour, but thats another bug and has kind of put me off.


Ill try and refine if Im feeling generous its taken me that long to get the form etc working I think its time to maybe get my holidays enjoyed, although I cant keep away from the problem. Besides that I sensibly left the rest of the project at work.

Cheers for your help on both issues

Neil