Move formula range

[Jayling01]

Good day all,

I have a SS project to book rooms. Rooms are allocated by rows and dates by columns. I have a VB which counts available or booked rooms by the cell colours. I would like to run the count color formula by the date entered in the first cell in each column, thus being able to jump from date to date and find how many rooms are available on a given date. Any pointers would be much appreciated.

Kind regards,

JJ

 

[Michael M]

Maybe it would help if you provided the code and a bit more info on how the worksheet is laid out ??

 

[Jayling01]

Thank you Michael for your swift reply. The SS is laid out as rows as room numbers and columns as dates. I would like to use the color count to sum rooms occupied or not (yellow is occupied, white is vacant). This works fine. Function CountColor(Rng As Range, RngColor As Range) As Integer
Dim Cll As Range
Dim Clr As Long
Clr = RngColor.Range("A1").Interior.Color
For Each Cll In Rng
If Cll.Interior.Color = Clr Then
CountColor = CountColor + 1
End If
Next Cll
End Function

I want to now select a colum by its date entry (column title) rather than adjusting the range in the formula.
Hope this makes sense.

Thanks again,
JJ

 

[Michael M]

A different approach might be not to have a formula at all
This will work on the whole column, by using the selected cell at the top.
You can either assign it to a button or run the code by Pressing ALT + F8 and selecting the macro
It assumes the "yellow" is standard VBYellow

Sub MM1()
Dim cell As range, color As Long
color = ActiveCell.Interior.color
For Each cell In range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
    If cell.Interior.color = vbYellow Then
        C = C + 1
    End If
Next cell
MsgBox "There are " & C & " rooms booked"
End Sub

 

[Jayling01]

Michael,

Thanks very much. I shall I put this to work tonight and post back.

Thanks again,

JJ