Line Colour

[pauleapo]

In the spreadsheet above, the red line is just a line i have created which i drag around to where it is needed and is not a cell border.

What i am wondering is it possible to make the cell border above for that line to change to red when the date is a monday for every week, i know i could do this with conditional format, but there can be multiple date which equals monday each week and i only want the top 1 to be red. hope this all makes sense

Regards
Paul

 

[PaddyD]

"...and i only want the top 1 to be red."

Top with respect to what? position in the column, first monday of the month?

 

[sunnyland]

Hello to you both,

To my understanding you want the first monday with a red line of the first Monday red for every week but every week may have more than one entry with day Monday

Let's say the monday are in column F start this formatting from F2 (not F1) to F65536 as it uses the previous cell to see if it is already a monday:
Using conditional formatting and then formula

=AND(WEEKDAY(F2,2)=1,WEEKDAY(F1,2)<>1)

For the first cell the conditional formatting will be
=WEEKDAY(F1,2)=1

This will work fine supposing you don't have 2 consecutive rows: as follow:

Monday 30 November
Monday 6 November
in this case testing for same week should be included as well but hopefully this case will not apply to you.

 

[pauleapo]

Thanks, right this has got me off to a great start and works, u must have understood my poor english (AND THAT IS MY NATIVE TONGUE...lol), only problems i have is that

1. for some reason because this spreadsheet already has grid lines they dont turn red (I know the con format works because i tested it with cell changing colour)

2. Is there any way i can change the thickness of the red line when it changes, conditional format doesnt seem to have any option for changing thkness of line on borders?

But thanx for the gr8 start to this, really hope some 1 can finish this 1 of for me


Cheers

 

[sunnyland]

Hello,


My native language is French so don't worry about your English,

Sorry when I tried out the formatting my cell didn't have a border so it was working perfectly.

I don't think you can set the conditional thickness of the border unless using vba code.

The border line only red on top is not possible because you can only change color as a whole [4 sides] and can't change each border individually.

However try this conditional formatting and see if it could do the trick:
We suppose the date are in column F still
It will be a double formatting,
Condition1)
If a Sunday and cell underneath Monday
=AND(WEEKDAY($F2,2)=7,WEEKDAY($F3,2)=1)
red border but only bottom border

Condition2)
If a Monday and previous cell Sunday
=AND(WEEKDAY($F2,2)=1,WEEKDAY($F1,2)=7)
black border but only bottom,right and left ,
black font and bold to attrack attention to the Monday Cell

That will give you the red at the top of the first Monday
The only border missing will be on the Sunday right and left border but if you can live with it That is a solution

Have a try and see if this works better for you

 

[pauleapo]

This works nearly perfect, the only problem now being is i use this for a Works Programme For Site deliverys. 90 percent of times we dont deliver on Sundays so therefore in this spreadsheet any day of the week can be before a monday. There is no rule as to what day will before the monday i want the line to appear Could be wednesday, could be thursday.... u get the jist.....

Sorry for being a pain

 

[pauleapo]

It is ok i think i have sorted this

just changing the conditional formats slightly as below

Condition1
=AND(WEEKDAY($F2,2)>2,WEEKDAY($F3,2)=1)

Condition2
=AND(WEEKDAY($F2,2)=1,WEEKDAY($F1,2)>2)

Yet again the only but being that there may be a small possibility that the first weekly date may not be a monday
This meaning the line will not be red if the first day be Tuesday



Do u know how i could alter the line thickness with VBA?

Cheers

Paul

 

[sunnyland]

Hello,

Do not use the conditional formatting [remove it] for the red line and try this code it should do what you want the thick line on top of Monday even if previous day is not Sunday.
Replace sheet1 by your sheet name.

The conditional formatting must be remove for this to work

Run PutARedLineAboveMonday, the code can go on the worksheet

Sub PutARedLineAboveMonday()
'replace sheet1 by the codename of your sheet
Dim rowi As Range
For Each rowi In Sheet1.Columns(6).Cells
If VarType(rowi) = vbDate Then
If Weekday(rowi, vbMonday) = 1 Then
entirerowredtop rowi.entirerow
End If
End If
Next
End Sub

Sub entirerowredtop(rowi As Range)
With rowi
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThick
.Borders(xlEdgeTop).ColorIndex = 3
End With
End Sub

 

[pauleapo]

Thanks for the quick reply,

I dont seem to be able to get this to work i put all the following code in the worksheet(in vba)

Sub PutARedLineAboveMonday() 
'replace sheet1 by the codename of your sheet 
Dim rowi As Range 
For Each rowi In Sheet1.Columns(6).Cells 
If VarType(rowi) = vbDate Then 
If Weekday(rowi, vbMonday) = 1 Then 
entirerowredtop rowi.entirerow 
End If 
End If 
Next 
End Sub 

Sub entirerowredtop(rowi As Range) 
With rowi 
.Borders(xlEdgeTop).LineStyle = xlContinuous 
.Borders(xlEdgeTop).Weight = xlThick 
.Borders(xlEdgeTop).ColorIndex = 3 
End With 
End Sub

But when i put dates in Column F nothing happens?

I have done this on a test woorkbook so using sheet1 and also there is no conditional formatting? What am i doing wrong?

 

[pauleapo]

ok i know see i have to run this as a macro ,

is there any way this can be done automatically ie: bychange or will this slow down everything?

Also this again puts a line above any monday(with the conditional formatting we managed to get it to pick up on the first monday of a week for example if we have two mondays of the same date it red lines them both),

Also is it possible to make this work for first date of the week if this not monday(my sheet is not always forced to be a monday as first day of week),

Also i notice that if dates are altered or sorted into different orders that when i run macro again it just puts new lines in and doesnt remove old ones so therefore i could have red lines over any day. this is quite important because i am constantly changing dates on this sheet

Also i only want the red line to go from column A to Column AL how can i implement this?


Sorry if i am asking for too much