Conditional Formatting

[D Willett]

Using the tutorial from:

http://www.mrexcel.com/articles/gantt-c ... atting.php" onclick="window.open(this.href);return false;

Having this working fine, changed the C-Formatting, selected the range so I could PasteSpecial but the menu item (PasteSpecial) is greyed out.
How do I get the menu back?

[HansV]

In order to Paste (Special) you must first copy something.

You can also use the Format Painter (paintbrush button) to copy conditional formatting (plus all other formatting):
- Select a cell with the correct formatting.
- Click the Format Painter.
- Select the range you want to apply the formatting to.

[D Willett]

Cheers Hans, managed to find where I was going wrong.
Any chance of helping with the same issue as post No 30996 ?
Also the ends of the formatting ( using the border tool ) aren't right as in the png, the start and end are open ended instaed of closed.

[StuartR]

...Also the ends of the formatting ( using the border tool ) aren't right as in the png, the start and end are open ended instaed of closed.

Can you attach an actual workbook, rather than a picture. Obviously you would need to remove any confidential data.

[HansV]

The dates in H1 etc. correspond to the first of each month.
The dates in columns C and D don't, so conditions 1 and 2 are never met.
What exactly do you want to do with partially covered months - colour them or not?

Which problem from post 30996 do you mean?

[D Willett]

Hi Stuart, please see attached, it's not confidential.
Appreciate the help.

Thanks

[D Willett]

Hi Hans, 31000 was where I needed help in not filling the cell if there is no date range, Stuarts formula was agreed by all.

------------------------------------------------------------------------------------------------------
The dates in H1 etc. correspond to the first of each month.
The dates in columns C and D don't, so conditions 1 and 2 are never met.
What exactly do you want to do with partially covered months - colour them or not?
------------------------------------------------------------------------------------------------------
I'm sure I followed the tutorial from http://www.mrexcel.com/articles/gantt-c" onclick="window.open(this.href);return false; ... atting.php correctly, obviously using my cell names instead !!
Perhaps I misunderstood it.
The start of the coloured bar should be closed, the middle open and the end closed so the complete bar looks continuous and surrounded by a full border.

Regards

[HansV]

There is no exact match between the dates in row 1 and those in columns C:D. The tutorial assumes that there is an exact match.
And you mistakenly used H2 in the second rule instead of H1.

Here are the correct formulas:
Rule 1: =AND(H$1<=$C2,I$1>$C2)
Rule 2: =AND(H$1<=$D2,I$1>$D2)

This will also solve the other problem.

[D Willett]

That is just absolutely perfect :-)

Just one more ( If I can be cheeky ) thing.
On opening the sheet, the date range starts from Jan 10 ( which has gone ) can the current month be in focus ?

Regards

[HansV]

Put the following code into the ThisWorkbook module. It assumes that the schedule sheet wil be the active sheet.

Code: Select all

Private Sub Workbook_Open()
  Dim d As Date
  Dim rng As Range
  d = Date - Day(Date) + 1
  Set rng = Range("H1:IV1").Find(What:=d)
  Application.Goto rng, True
End Sub

You could also use the Worksheet_Activate event; if you prefer that, put the following code in the worksheet module:

Code: Select all

Private Sub Worksheet_Activate()
  Dim d As Date
  Dim rng As Range
  d = Date - Day(Date) + 1
  Set rng = Range("H1:IV1").Find(What:=d)
  Application.Goto rng, True
End Sub

[D Willett]

Fantastic.... this opens so many opportunities :-)

Have a great weekend, and thanks also to StuartR

Many Regards