Format Help needed

[JohnJay]

Does anyone know if there is a way to spit out all the formatting and formula info for a row? What I would like to do is be able format an entire row on the fly if a user clicks on a row that is not formated.

thanks John

 

[JohnJay]

**** juan, I can hide that row in the addin sheets...LOL Someone just hit me with a shovel...LOL

 

[Juan Pablo González]

Ok. AddIn's are just "hidden" workbooks. So, you can have a preformatted row in the AddIn. Test with something like:

If ActiveSheet.UsedRange.Count = 1 then
'It's an empty sheet
else
'Not an empty sheet
End If

How about that ?

That condition could also be:

Cells.SpecialCells(xlCellTypeLastCell).Address = "$A$1"

or

Application.Counta(ActiveSheet.UsedRange) = 0

I like the best this last one.

 

[Derek]

Hi John

This may not be exactly what you are looking for but it may give you some ideas.

Say your rows all have the same format and formulas (ie they can be scrolled down) and they start at row #, then try this:

Insert a row at the top of your worksheet. Copy row # and paste it to row 1. Hide row 1.
Paste this code to a macro that runs from a button on your worksheet:
Set x = ActiveCell.EntireRow
On Error Resume Next
Application.ScreenUpdating = False
Rows("1:1").Copy
x.Select
Selection.Insert Shift:=xlDown
ActiveCell.EntireRow.Hidden = False
End Sub
Title the button "Insert New Row" Provide some instruction..."to insert new row: First select row then click this button (new row will insert above row selected)".
You could force use of this by protecting your sheet and having the macro remove protection, run, then reprotect.

Hope this is of some help
regards
Derek

 

[JohnJay]

I think that will do for the blank worksheet. now is there a way to tell if a whole row is empty?

 

[Juan Pablo González]

Application.Counta(Rows(1)) = 0

 

[JohnJay]

Juan,

I tried that last one and that works for blank sheets. What I was looking for is that if a user clicks on a row, and it is not already formated like I mentioned before, then format it, else do nothing. I thought about checking at least 3 cells in the row for a match of what is suppose to me there, but I thought there might be a simpler way to check the format againts the one in the addin and then proceed on that finding.

 

[Juan Pablo González]

Again, sorry. There's not. You'll have to loop, and you can use the SelectionChange() to trigger that event. If you think you can check only three cells then this would not take long.

This stuff may be needed:

.Font.Bold
.Font.Italic
.Font.Color
.Interior.Color

 

[JohnJay]

Actuall I do have 3 cells that are colored, and I can always add in a font color to check....Thats great..
Thanks juan