VBA adding borders
 

I'm using Excel 2003

I have a range of cells ("B27:R63")
some of which are RGB(255, 128, 128) These cells will change every week.

I have a CommandButton1_Click that needs to go through the range and find
those colored cells and
replace the color to none and add diagonal borders

LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

Can someone please help with a Macro so I don't have to do this by hand
twice a week for the next seventeen weeks?
My pea brain got close a couple of times but I got frustrated and deleted
the entire thing to start over. Thought I'd ask for help first.

Thanks
CR

VBA adding borders
 

CR;490002 Wrote:
I'm using Excel 2003

I have a range of cells ("B27:R63")
some of which are RGB(255, 128, 128) These cells will change every
week.

I have a CommandButton1_Click that needs to go through the range and
find
those colored cells and
replace the color to none and add diagonal borders

LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

Can someone please help with a Macro so I don't have to do this by
hand
twice a week for the next seventeen weeks?
My pea brain got close a couple of times but I got frustrated and
deleted
the entire thing to start over. Thought I'd ask for help first.

Thanks
CR

Something on these lines?:Sub blah()
For Each cll In Range("B27:R63").Cells
If cll.Interior.Color = RGB(255, 128, 128) Then
cll.Interior.ColorIndex = xlNone
With cll.Borders(xlDiagonalUp)
LineStyle = xlContinuous
Weight = xlThin
ColorIndex = xlAutomatic
End With
End If
Next cll
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=135086

VBA adding borders
 

Be sure the sheet you want to change is the active sheet. I assume you know
how to tie the code to the button.

Private Sub CommandButton1_Click()
For Each c In ActiveSheet.UsedRange.Cells
If c.Interior.ColorIndex 0 Then
c.Interior.ColorIndex = xlNone
With c.Borders(xlDiagonalDown)
.LineStyle = xlContinious
.Weight = xlThin
End With
End If
Next
End Sub



"CR" wrote in message
m...
I'm using Excel 2003

I have a range of cells ("B27:R63")
some of which are RGB(255, 128, 128) These cells will change every week.

I have a CommandButton1_Click that needs to go through the range and find
those colored cells and
replace the color to none and add diagonal borders

LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

Can someone please help with a Macro so I don't have to do this by hand
twice a week for the next seventeen weeks?
My pea brain got close a couple of times but I got frustrated and deleted
the entire thing to start over. Thought I'd ask for help first.

Thanks
CR

VBA adding borders
 

If you only want to change the cells with that specific color then the
ColorIndex equivalent for RGB(255, 128, 128) is 22. So:

Private Sub CommandButton1_Click()
For Each c In ActiveSheet.UsedRange.Cells
If c.Interior.ColorIndex = 22 Then
c.Interior.ColorIndex = xlNone
With c.Borders(xlDiagonalDown)
.LineStyle = xlContinious
.Weight = xlThin
End With
End If
Next
End Sub




"JLGWhiz" wrote in message
...
Be sure the sheet you want to change is the active sheet. I assume you
know how to tie the code to the button.

Private Sub CommandButton1_Click()
For Each c In ActiveSheet.UsedRange.Cells
If c.Interior.ColorIndex 0 Then
c.Interior.ColorIndex = xlNone
With c.Borders(xlDiagonalDown)
.LineStyle = xlContinious
.Weight = xlThin
End With
End If
Next
End Sub



"CR" wrote in message
m...
I'm using Excel 2003

I have a range of cells ("B27:R63")
some of which are RGB(255, 128, 128) These cells will change every week.

I have a CommandButton1_Click that needs to go through the range and find
those colored cells and
replace the color to none and add diagonal borders

LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

Can someone please help with a Macro so I don't have to do this by hand
twice a week for the next seventeen weeks?
My pea brain got close a couple of times but I got frustrated and deleted
the entire thing to start over. Thought I'd ask for help first.

Thanks
CR

VBA adding borders
 

Thank you both
I put my Pea Brain back on it and finally did get it to work but..........

Mine has about fifty more lines than either one of yours.

Think I'll use yours.

Thank you again.


"CR" wrote in message
m...
I'm using Excel 2003

I have a range of cells ("B27:R63")
some of which are RGB(255, 128, 128) These cells will change every week.

I have a CommandButton1_Click that needs to go through the range and find
those colored cells and
replace the color to none and add diagonal borders

LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

Can someone please help with a Macro so I don't have to do this by hand
twice a week for the next seventeen weeks?
My pea brain got close a couple of times but I got frustrated and deleted
the entire thing to start over. Thought I'd ask for help first.

Thanks
CR

VBA adding borders
 

On Sep 15, 7:10*pm, "JLGWhiz" wrote:
Be sure the sheet you want to change is the active sheet. *I assume you know
how to tie the code to the button.

Private Sub CommandButton1_Click()
* For Each c In ActiveSheet.UsedRange.Cells
* * * If c.Interior.ColorIndex 0 Then
* * * * * c.Interior.ColorIndex = xlNone
* * * * * With c.Borders(xlDiagonalDown)
* * * * * * .LineStyle = xlContinious
* * * * * * .Weight = xlThin
* * * * * End With
* * * End If
* *Next
End Sub

"CR" wrote in message

m...

I'm using Excel 2003

I have a range of cells ("B27:R63")
some of which are RGB(255, 128, 128) These cells will change every week..

I have a CommandButton1_Click that needs to go through the range and find
those colored cells and
replace the color to none and add diagonal borders
how is code tied to a button?
button1.onclick="mymacro"?

thanks


LineStyle = xlContinuous
* * * *.Weight = xlThin
* * * *.ColorIndex = xlAutomatic

Can someone please help with a Macro so I don't have to do this by hand
twice a week for the next seventeen weeks?
My pea brain got close a couple of times but I got frustrated and deleted
the entire thing to start over. Thought I'd ask for help first.

Thanks
CR