Highlighting Rows with VB.

[Xman]

To anyone,

This is the current formula that I am using to highlight rows with:

-- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
With .Borders(xlBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
End With
..FormatConditions(1).Interior.ColorIndex = 8
End With

End Sub

Works well...problem, is it removes any coditions that exsist in oter areas
of the SS.
And when I protect the page, the macro no longer works.

Is there any way to modify this command so that a column is skiped? For
instance;
I want the entire row with the exception of column "x". Can this be done?
Any help would be awesome!!!! Thanks,

JARoman

 

[Susan]

i would leave your macro alone, with the exception of adding this at
the bottom (after "End With"):

worksheets("Sheet1").range("x:x").ClearFormats

you may need to ammend the sheet name or whatever to make it work in
your macro. but that would be the simplest idea, i believe.
make sure you save a copy of your worksheet before trying it, in case
it doesn't do what you want it to. of course this will also remove
formats that had been there previously, so it may not be what you
want.
hope that helps!

susan

 

[Xman]

Hi Susan,

Thank you for your suggestion.....It worked great on a blank spread sheet.
But when I applied it to the one I'm working on, it just didn't work. I'm
very new to this whole VB stuff, but hopefully with folks like you willing to
assist I'll find the correct answer. Thanks again.

 

[Susan]

it did absolutely nothing at all??? but it worked in a new
worksheet?

that would imply that your sheet name is not "Sheet1", which it would
be in a new workbook. if it's named something different, you'll have
to enter the correct name in the macro line.
susan

 

[Xman]

Hi Susan.....you are right! I had named the sheet something other than
"sheet...." and therfore did not work. You are also right in saying that any
conditions or formats will be eliminated from that column, thus the reason
for the original question, but it's getting closer. Now....if I can just get
those conditions and formats back in there.

Thank you.....

 

[Gord Dibben]

Susan

If you use this Me.range("x:x").ClearFormats

Me refers to the activesheet so sheetname not required.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

This may get you a little closer but does clear some formatting on just the
selected row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
With OldCell.EntireRow
.Interior.ColorIndex = xlColorIndexNone
.Borders.LineStyle = xlLineStyleNone
End With
End If
Set OldCell = Target
With OldCell.EntireRow
.Interior.ColorIndex = 6
.Borders.LineStyle = xlContinuous
End With
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
Me.Range("X:X").ClearFormats
ActiveSheet.Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP

 

[Susan]

Gord -
rats, didn't think of that.

susan

On Jan 21, 3:50 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
Susan

If you use this       Me.range("x:x").ClearFormats

Me    refers to the activesheet so sheetname not required.

Gord Dibben  MS Excel MVP

 

[Xman]

Thanks guys for all your help. Gord, I'm getting ready to try out your
formula with Susans' suggestion. I'll keep you all posted.

Thank you,

 

[Xman]

Gord, your formula did allow me to lock the worksheet, but as you stated..it
erased everything as I moved the highlight.The original formula with Susans
helpful addition would be ideal if I could get it to leave the conditional
formats I have in column "x" alone and if I could get it to work when it's
protected it would be perfect.
This has become a real obssesion for me because I know there has to be a way
to do it. I'm gonna keep trying based on the original formula. I really
appreciate all of your imput, Gord/Susan.

Thanks

 

[Susan]

the only thing i can think to do is to record a macro re-installing
the conditional formats you want in column X. when you record the
macro it will put it in a separate module. then, below the clearing
of all formats in column X, put

Call Macro2 'or change it to whatever the name of
'the macro you recorded is

then it will re-install all the conditional formats. a pain to do,
but at least you'd only have to do it once & then the macro would
automatically do it for you each time.

susan

 

[Gord Dibben]

I misunderstood. I thought you wanted the formats cleared from column X and
used Susan's Range("X:X").ClearFormats

The code I posted will not clear the CF formatting in any column or cell if
you remove the line Me.Range("X:X").ClearFormats which I have done in
this revision.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
With OldCell.EntireRow
.Interior.ColorIndex = xlColorIndexNone
.Borders.LineStyle = xlLineStyleNone
End With
End If
Set OldCell = Target
With OldCell.EntireRow
.Interior.ColorIndex = 6
.Borders.LineStyle = xlContinuous
End With
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub


Gord

 

[Xman]

You're brilliant!!!!!! It's working the way I want it too.....with the shhet
protected it works, the conditional formats remain in the x column. But why
does it wipe out my sheet of all gridlines and color schemes, kind of like an
eraser. My sheet turns entirely white with the exception of any fonts and
the CF colors?