Shared Spreadsheet cell colours change

[Ryanelmdale]

We have a shared spreadsheet setup between two of us. We are using a VBA and a colour formula. (these will be stated at the bottom)
Basically the formula looks at a specific column and looks for a specific colour, it then counts up that colour and displays it within the specified cell.

While sharing this file if the other user adds that lime green in that column and then saves the file and the original user looks at that cell, the colour is like a dark greeny brown. This is then not picked up on the formula as it is a different colour. We would like it to stay the same colour. Please note this isn't a border it is the whole cell.

If we unshare the file it goes back to the normal colour it should be.
The track changes on/off doesn't seem to help.

Any ideas?

VBA:

Function CountCcolor(range_data As range, criteria As range) As Long
Dim datax As range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function

Formula:

=CountCcolor(J:J, N4)

 

[Meggesto]

I believe the issue may lie with the Color Index. The color index may be different between users and/or computers. I would try using RGB color coding.

 

[Ryanelmdale]

I believe the issue may lie with the Color Index. The color index may be different between users and/or computers. I would try using RGB color coding.

How would i set these to be the same if they are different?

 

[Meggesto]

I'm wondering if instead of using ColorIndex use just Color would alleviate the problem. So these two lines:

xcolor = criteria.Interior.ColorIndex
If datax.Interior.ColorIndex = xcolor

Change To:

xcolor = criteria.Interior.Color
If datax.Interior.Color = xcolor

See if that makes a difference?

 

[Ryanelmdale]

I'm wondering if instead of using ColorIndex use just Color would alleviate the problem. So these two lines:

xcolor = criteria.Interior.ColorIndex
If datax.Interior.ColorIndex = xcolor

Change To:

xcolor = criteria.Interior.Color
If datax.Interior.Color = xcolor

See if that makes a difference?

So this sort of worked but as soon as the shared user made an edit that colour changed back to the darker colour on the other users side.

I am curious as to whether the link below will help?
vba - Excel Countifs formula with one color criteria and the rest normal - Stack Overflow

 

[Meggesto]

So I have a few questions. Do you always highlight in one color only? Could you count the cells that have no color and subtract them from the total cells to leave only those with color; this way color doesn't really matter only colorless cells do? Can you check the RGB color code for each user to see if the colors are actually the same but the monitors display them differently?

 

[Ryanelmdale]

So I have a few questions. Do you always highlight in one color only? Could you count the cells that have no color and subtract them from the total cells to leave only those with color; this way color doesn't really matter only colorless cells do? Can you check the RGB color code for each user to see if the colors are actually the same but the monitors display them differently?

Do you always highlight in one color only? - No
Could you count the cells that have no color and subtract them from the total cells to leave only those with color - No as there are multiple colours.
Can you check the RGB color code for each user to see if the colors are actually the same but the monitors display them differently? - No because the colours are correct on each machine but when the other user changes a yellow to the green it goes to a darker green on the main users excel but normal on the original and same both ways.

 

[Meggesto]

I apologize as I don't seem to have an answer for why this is happening. In my research it looks as if Shared Workbooks encounter bizarre activity occasionally. This may be one of those instances. Could there be a different way of marking and counting that could work for you? Possibly using DATA VALIDATION and a LIST function? Then use COUNTIF formulas?

 

[Ryanelmdale]

I apologize as I don't seem to have an answer for why this is happening. In my research it looks as if Shared Workbooks encounter bizarre activity occasionally. This may be one of those instances. Could there be a different way of marking and counting that could work for you? Possibly using DATA VALIDATION and a LIST function? Then use COUNTIF formulas?

No worries, you have been a great help!
After validating the versions were the same and there wasn't any issues with updates or anything i decided to start a fresh spreadsheet and copy the data across. I then added in the VBA and custom formula to which it decided to work. one one machine it didn't pick up the VBA however we are monitoring this to see if it eventually picks it up and if it was a little slow.

 

[Ryanelmdale]

All fixed!!!