Formatting issues

[paddy69]

I have an unformatted datasheet (from a non MS application) that I copy to Excel. With VBA I want to format the sheet so that it's easier to read.
So far I have came up with some of the code (see attachment) but could anyone help me with the missing parts?

The first sheet is the result of my code so far.
The second sheet is the unformatted data.
The third sheet is the desired situation.

Summarizing the desired situation:

• I want to add a blank row between two rooms.
• Rooms should always have a grey filling and be underlined no matter what the roomnumber is.
• The cells in the Room rows should have a green filling except when there's a double booking. In that case it should be red.
• I want to add a row (first row) with the month name (e.g. August) and merge all cells for that month.
• I want to add a row (second row) for weeknumber (e.g. 33) and merge all cells for that week.

Thanks,
Paddy.

[mdmackillop]

The Desired Situation does not seem to reflect your descriptions above.
How are double bookings identified?

[paddy69]

Double bookings are applicable if within one room, two projects are scheduled on the same day. E.g. Room 1, August 20, Project 1 and 2. Please ignore the number of hours in the cells.

Thanks,
Paddy69

[mdmackillop]

So Project 1 & 2 is double booking, but 1 & 4 is not? (Room 3 20/8)
You need to be very specific.

[Bob Phillips]

Didn't we answer this question last week?

[mdmackillop]

Project creep!

[paddy69]

You did solve the first step which basically is still in the code as you can see. Now I'm trying to make it more readible. I didn't know that that wasn't allowed?

Thanks anyway for your previous help. Guess I have to keep trying to find the answer myself.

[mdmackillop]

Hi Paddy, That was not the intention of the comment. Most questions develop from the original posting. It's expected! Happy to continue with your follow up. So the answer to Post #4 is?

[paddy69]

I guess I missed a few cells when updating manually! That's another good reason why this should be automated. Anyway, I missed to mark cell K16, K17 and T10-T12 in red as these are also double booked.

Many thanks so far!

-Paddy69

[mdmackillop]

Here's most of it.
[VBA] Option Explicit
Public Sub RandCol()

Dim rng As Range, Col As Long, Cel As Range, c As Range, FirstAddress As String
Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Col = Int(Rnd * 10)
For Each Cel In rng
If Cel.Interior.ColorIndex = xlNone Then
Col = Col + 1
With rng
Set c = .Find(Cel, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address

Do
c.Interior.ColorIndex = Col
c.Offset(, 4).Resize(1, 252).SpecialCells(xlTextValues).Interior.ColorIndex = Col
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End If
Next
Formatting
End Sub

Sub Formatting()
Dim rng As Range, Cel As Range, Rg As Range, c As Range
Dim Cnt As Long, Cols As Long, FirstAddress As String
Rows("1:2").Insert
Set rng = Range(Cells(3, 5), Cells(3, 5).End(xlToRight))
Cols = rng.Cells.Count
'Create month names
rng.Offset(-2).FormulaR1C1 = "=Text(R3C,""MMMM"")"
rng.ColumnWidth = 2
Application.DisplayAlerts = False
'Merge Month names
Set Rg = Cells(1, 5)
For Each Cel In rng.Offset(-2, 1)
If Cel = Cel.Offset(, -1) Then
Set Rg = Union(Rg, Cel)
Else
Rg.MergeCells = True
Set Rg = Cel
End If
Next
'Create weeknumbers
rng.Offset(-1).FormulaR1C1 = "=Weeknum(R3C)"
'Merge Week Numbers
Set Rg = Cells(2, 5)
For Each Cel In rng.Offset(-1, 1)
If Cel = Cel.Offset(, -1) Then
Set Rg = Union(Rg, Cel)
Else
Rg.MergeCells = True
Set Rg = Cel
End If
Next

With Columns(1)
Set c = .Find("Room", LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If Not (FirstAddress = c.Address) Then c.EntireRow.Insert
c.EntireRow.Offset(-1).Interior.ColorIndex = xlNone
Set rng = c.Offset(, 4).Resize(1, Cols)
For Each Cel In rng
Cnt = 0
For Each Rg In Cel.Offset(1).Resize(4, 1)
If Rg.Interior.ColorIndex <> xlNone Then Cnt = Cnt + 1
Next
If Cnt > 1 Then Cel.Interior.ColorIndex = 3
Next
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

Columns("B:B").Insert

With Columns("B:B")
.Interior.ColorIndex = xlNone
.Columns.ColumnWidth = 3
End With
Columns("C:E").ColumnWidth = 0
Range("A1").Select
End Sub[/VBA]

[paddy69]

I quickly checked but so far it looks very good. Tomorrow I will integrate it with the other code.

Thanks again and my apologies for the misunderstanding.

-Paddy69

[paddy69]

It's been a while but I still have to finish this project. Can anyone help me?

1. I had to add an extra column (column B). This contents of this column has to be grouped as well.
2. Empty columns on the right and left must be hidden. The date range has to be continiously so no columns in the middle may be hidden.
3. Borders around the months and weeks
4. A legend. I prefer a pop-up (message box) but if that's not possible a legend at the bottom will do.
5. Overallocation in red. There's is some code in the project but it doesn't work anymore.
6. A grouping per Room.
7. I was warned about the colors but at first I ignored that. Obviously that was wrong. I'm wondering can I use a predefined list of let's say 10 different colors? i was thinking about an array but have no idea how to update the code for this.
8. The colors of the projects should be transferred to the summarized level.

Thanks in advance for your help!!!!

[paddy69]

I have replied to an old post. Could anyone help out?

Many thanks!

http://www.vbaexpress.com/forum/showthread.php?t=14692

[Norie]

If you have another thread then please stick to it.

If what's been suggested there doesn't work then explain why it doesn't.

[lucas]

Threads merged....it is usually better to bump your thread by posting in it again than to start a new thread. Please read our faq.

[paddy69]

I wasn't sure what to do and I quickly checked the FAQ's but I couldn't find any guidance. Therefore I first added a post to the old thread but when nobody answered I created a new thread and linked it to the old one.

Anyway, I did manage to solve a few issues myself so I'm hoping anyone can help me solve the other issues, one by one.

I want the resource name (and rows) not to be colored like the project rows.
The cells in the resource name row should have the same color as the projects falling under the resource name (it's a merge). Only if two projects are planned on the same day, the cell in the resource name row should be red.
After that I want the project rows to be grouped so that only the resource name row is visible. Only if you see any red cells in the resource name row, you can ungroup the projects in that section.

Thanks!