Conditional Format borders

kev6264

New Member
Joined
Jun 16, 2006
Messages
44
Surely this is easy for this forum. I have a spreadsheet (Calendar) that I created and have it conditional format borders when the correct days of the month are displayed. Works great in 2010/2007, but borders won't display in 2003. Want to create a VBA solution to create the same enhancement. I have posted the spreadsheet out here... https://www.box.com/s/8997557bd3c209ad3f55. I know very little VBA, but can figure out most stuff if I study it. If someone can get me started, I can surely finish. Thanks in advance.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Realizing my original post was a little too much for a quick answer so I will try and reword.

I have this code copied below. Works great, but want to run the condition on 36 different non-contingious cells which would create a huge code. I was trying to figure out how to have the borders turn on or off based on a condition of 1 or 2. 1 = borders on, 2 = borders off. Can use conditional format, but most of the people using this have 2003 and CF limited and does not include borders. I have re-attached a simple example spreadsheet with this same code below. If I'm still not clear enough please let me know what I need to include to make my question clearer.



https://www.box.com/s/aa74487e5f77dc667156


Sub BorderOn()


Dim intBordersOn As Integer
Dim StartRange As Range
Dim NewRange As Range


Set StartRange = Range("e2")

Set NewRange = StartRange.Offset(0, 0)

intBordersOn = NewRange

NewRange.Select

If intBordersOn = 1 Then

ActiveCell.Offset(1, 0).Range("A1:B6").Select

With Selection.Borders
.Weight = xlThin
.Color = vbBlack
End With

With Selection.Interior
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With


ActiveCell.Offset(-1, 0).Range("A1").Select

With Selection.Interior
.Color = 13434828
End With

With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.Color = 0
End With

Else: intMonth = 2

ActiveCell.Offset(1, 0).Range("A1:B6").Select

With Selection.Borders
.Weight = xlThin
.Color = vbWhite
End With

With Selection.Interior
.Color = RGB(255, 255, 255)
End With

With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

With Selection.Font
.Color = 2
End With


ActiveCell.Offset(-1, 0).Range("A1").Select

With Selection.Borders(xlEdgeBottom)
.Weight = xlThin
.Color = vbBlack
End With

With Selection.Interior
.Color = RGB(255, 255, 255)
End With

ActiveCell.Offset(7, 0).Range("A1").Select

With Selection.Borders(xlEdgeTop)
.Weight = xlThin
.Color = vbBlack
End With



End If
End Sub
 
Upvote 0
Here is my code. 2003 doesn't like boCompare = Range("R16").Value
get a 2015 error. Any suggestions?



<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> BorderShow()<br><br>ActiveCell.Offset(1, 0).Range("A1:B6").Select<br>********<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Borders<br>************.Weight = xlThin<br>************.Color = vbBlack<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Interior<br>************.ColorIndex = 15<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<br>********<br>********<br>****************<br>ActiveCell.Offset(-1, 0).Range("A1").Select<br>****<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Interior<br>************.Color = 13434828<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>****** <br>********<SPAN style="color:#00007F">With</SPAN> Selection.Font<br>************.Color = 0<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Borders<br>************.Weight = xlThin<br>************.Color = vbBlack<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> BorderNoShow()<br><br>ActiveCell.Offset(1, 0).Range("A1:B6").Select<br>**<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Borders<br>************.Weight = xlThin<br>************.Color = vbWhite<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>****************** <br>********<SPAN style="color:#00007F">With</SPAN> Selection.Interior<br>************.Pattern = xlNone<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<br>************** <br>********<br>**** ActiveCell.Offset(-1, 0).Range("A1").Select<br>********<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Borders<br>************.Weight = xlThin<br>************.Color = vbWhite<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<br>********<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Interior<br>************.Color = RGB(255, 255, 255)<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Font<br>************.Color = RGB(255, 255, 255)<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<br>****ActiveCell.Offset(7, 0).Range("A1").Select<br>****<br>********<SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeTop)<br>************.Weight = xlThin<br>************.Color = vbBlack<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> BorderYesNo()<br><br><SPAN style="color:#00007F">Dim</SPAN> boCompare <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> intBordersOn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> intCounter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> intSubTotal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> StartRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> NewRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> rngCompare <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> rngNextCompare <SPAN style="color:#00007F">As</SPAN> Range<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> StartRange = [B16]<br><SPAN style="color:#00007F">Set</SPAN> NewRange = StartRange.Offset(0, 0)<br><SPAN style="color:#00007F">Set</SPAN> rngCompare = [R16]<br><br>intSubTotal = 2<br>intCounter = 0<br><br><SPAN style="color:#00007F">If</SPAN> [d4] = "Select a Month" <SPAN style="color:#00007F">Then</SPAN><br><br>Else:<br>****<br>****<br><br>boCompare = Range("R16").Value<br><br><br>NewRange.Select<br><br><br><SPAN style="color:#00007F">If</SPAN> boCompare = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br><br>****<SPAN style="color:#00007F">Call</SPAN> BorderShow<br>****<br>**<br>**Else: intCompare = <SPAN style="color:#00007F">False</SPAN><br>**<br>****<SPAN style="color:#00007F">Call</SPAN> BorderNoShow<br>****<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>intCounter = intSubTotal + 0<br><br><br><SPAN style="color:#00007F">Set</SPAN> NewRange = StartRange.Offset(0, intCounter)<br><SPAN style="color:#00007F">Set</SPAN> rngNextCompare = rngCompare.Offset(0, intCounter)<br><br><br>intBordersOn = NewRange<br>boCompare = rngNextCompare<br><br>NewRange.Select<br><br><SPAN style="color:#00007F">If</SPAN> boCompare = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br><br>****<SPAN style="color:#00007F">Call</SPAN> BorderShow<br>****<br>**<br>**Else: boCompare = <SPAN style="color:#00007F">False</SPAN><br>**<br>**<br>****<SPAN style="color:#00007F">Call</SPAN> BorderNoShow<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,211,867
Messages
6,104,460
Members
447,910
Latest member
abrook1308

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top