any vba/excel experts around

that

Just an idea and use libreoffice, so cant try it out. Rather than a colour assign a value in multiples of 10
Like Annual Leave is 1, Awating a/l Authorisation is 10, Bank holiday a/l is 100 etc and colour later. Easy to add up, only one value.

if it was a list rather than a table, pivot table may have been an easy answer?

Heedtheadvice

Clearly that is due to the double counting mentioned.


In my understanding:



The colours/colour index is for 56 colours. As is shown in your rgb data the multiple colours have a value of 0


Suggest that you also include a test of the interiors.pattern, patterncolour or pattern index.


I cannot remember those that you use so you may have to use a watch in code to find them and some more logic in your code. see https://www.thespreadsheetguru.com/the-code-vault/2014/2/27/cell-fills-color-patterns-gradients
for some good help on those properties.


Good luck.



Must admit I like your cell colouring to highlight those mixed day types but have to say I would prefer a different approach. To colour the cells based upon a test (whatever that might be in your sheet) and then to use the same test within the counting function rather than testing for colour/mix...


or even better as suggested by @That, to use a pivot table and list. I find it much easier to separate the raw data (in this case as a list/database)on one sheet in a format that is easy to use/test/analyse and from that produce the information or display which would mean a list of dates and 'activity' plus
counts summary as a pivot and separate weekly display sheet if you so wish on another sheet. If you need that weekly format for easy user entry or something then the list sheet can be made from the weekly sheet - and have another list that defines your public holidays? The data list dates can have cells that automatically find the day of the week.


Though all that is a fair bit more work than what you presently have!

WaywardDriver

To go back a step - how was the colour coding done? Manually / from some other program / Conditional Formatting / VBA code? If either of last 2 then may be options.

dipsomaniac

WaywardDriver wrote: »

To go back a step - how was the colour coding done? Manually / from some other program / Conditional Formatting / VBA code? If either of last 2 then may be options.

thanks for replies. the colour coding was done manually

trying to keep it simple. wondering if it would be easier to count if i replaced shift times with 'A/L' on all annual leave days. would i then be able to change the formulae to search for colour + A/L?

Heedtheadvice

I still feel it would be better (especially as you want simpler!) to go for the list approach you then can use conditional formatting too to set colours....let Excel do the work with built in functions etc. ............




However for your "A/L" idea; rather than getting rid of shift times (if that is useful) why not add "A/L" to the cell text (or add as a comment?) then you can search for your colour and contains "A/L" in your test or that in the comment property?

WaywardDriver

A tip with VBA is to use Record Macro to add the colour gradient coding and then edit the code generated. This produced a ColorStops property which I'd never met before. Even then it wasn't straightforward but this seems to work:


Function CountCcolor(range_data As Range, criteria As Range)
Dim datax As Range
If criteria.Interior.Pattern = xlSolid Then
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then CountCcolor = CountCcolor + 1
Next
Else
Dim xcol(2), ycol(2) As Long
Dim n As Integer
n = 0
For Each cs In criteria.Interior.Gradient.ColorStops
n = n + 1
xcol(n) = cs.Color
Next
For Each datax In range_data
If datax.Interior.Pattern <> xlSolid Then
n = 0
For Each cs In datax.Interior.Gradient.ColorStops
n = n + 1
ycol(n) = cs.Color
Next
If ycol(1) = xcol(1) And ycol(2) = xcol(2) Then CountCcolor = CountCcolor + 1
End If
Next
End If
End Function


Without trying to figure out the code, you could temporarily try replacing your code with this and see what happens.
Even if it does work, I'd be tempted to try to get VBA code to insert the colour coding and count at same time, as the manual process must be time consuming.

Heedtheadvice

Of course Wayward! I had forgotten about recording a macro. Doh!
I too had not come across colour stops before. I had a quick look at the object model yesterday but had not looked at that property (just the pattern ones) and recording give the answer to which property entry is selected during that manual cell fill operation.
.......great name for that property colourstops :rotfl:



There Dispso, you should now be able to make progress.


I still think (a bit like wayward) that manual setting is not the way to get the job done easily and as usual with Excel there are many ways to skin a cat!

dipsomaniac

thanks waywarddriver for this. i replaced my code with your code but get '#value!' for the two combined colour counts.

i haven't tried using record macro to add the gradient yet as i am unsure how to do it but will find out.

WaywardDriver

dipsomaniac wrote: »

thanks waywarddriver for this. i replaced my code with your code but get '#value!' for the two combined colour counts.

i haven't tried using record macro to add the gradient as i am unsure how to do it.

I kept getting #value when developing the code before getting it to work when tested on a cut-down version of yours with 1 solid and 1 gradient cell. If you upload your Excel file I can test properly.

Rather illogically Record Macro is under the View menu.

dipsomaniac

thank you. i have pm'ed you a dropbox link to the spreadsheet without any vba script