VBA adding borders

C

CR

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
 
P

p45cal

CR;490002 said:
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
Click to expand...

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
 
J

JLGWhiz

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
 
J

JLGWhiz

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
 
C

CR

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.
 
J

Jeffrey Whitley

Be sure the sheet you want to change is the active sheet.  I assume youknow
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





how is code tied to a button? button1.onclick="mymacro"?

thanks
Click to expand...
 
Top