Conditional Formatting not working anymore - Macro enable workbook

Being a little anecdotal, this is a bit like walking into a garage and saying your car is parked at home, 15 miles away, and it "isn't working". There's only so many questions you can ask to try to determine what might be wrong, with no way of checking and eliminating the possibilities.

And the mechanic isn't going to want to waste much time on it because a) he's not going to get paid for his time and b) he's got better things to do with his time.

18+ mechanics have looked at your thread so far ...

Regards, TMS

I'm not saying that I possess all, or even a small percentage of all knowledge, or that you are stupid, or that all around me are stupid ... however you want to extend that theme.

And, as it happens, I am of an age where I qualify for a free pass on public transport, so I am actively trying to use that instead of the car (although it isn't broken, but it is parked in the drive).

What I was saying is that there comes a point where you would need to get your car on the back of a tow truck and get it down to the garage where a mechanic could look at it, test it, diagnose the fault and, hopefully, fix it for you ... albeit, at a price.

In this instance, I think you need to let people have a look at a sample workbook so that it can be tested and tried. In theory, there should be no reason why CF should work differently in an .xlsx or .xlsm file. But then, when you put the CF condition(s) in, you might have referred to the wrong range(s). Or you may have a User Defined Function (UDF) that is not available in the .xlsx file, and which you depend upon.

Like I said, you can only guess at so many possible options.

Anyway, 38 mechanics have looked at the thread so far, though that could includes some repeat visits.

You said ...

... and only thing I have concluded is that this CF formula works in a .xlsx file and not in .xlsm file

You haven't even told us what that CF formula is ... maybe that would give someone a clue?

You should perhaps bear in mind that the people who try to answer questions on this forum, and others, do so voluntarily. That's something you need to consider when you respond to well meant, though maybe not well put, guidance with what I would interpret as "attitude".


Regards, TMS

Originally Posted by TMShucks

I'm not saying that I possess all, or even a small percentage of all knowledge, or that you are stupid, or that all around me are stupid ... however you want to extend that theme.

And, as it happens, I am of an age where I qualify for a free pass on public transport, so I am actively trying to use that instead of the car (although it isn't broken, but it is parked in the drive).

What I was saying is that there comes a point where you would need to get your car on the back of a tow truck and get it down to the garage where a mechanic could look at it, test it, diagnose the fault and, hopefully, fix it for you ... albeit, at a price.

In this instance, I think you need to let people have a look at a sample workbook so that it can be tested and tried. In theory, there should be no reason why CF should work differently in an .xlsx or .xlsm file. But then, when you put the CF condition(s) in, you might have referred to the wrong range(s). Or you may have a User Defined Function (UDF) that is not available in the .xlsx file, and which you depend upon.

Like I said, you can only guess at so many possible options.

Anyway, 38 mechanics have looked at the thread so far, though that could includes some repeat visits.

You said ...

You haven't even told us what that CF formula is ... maybe that would give someone a clue?

You should perhaps bear in mind that the people who try to answer questions on this forum, and others, do so voluntarily. That's something you need to consider when you respond to well meant, though maybe not well put, guidance with what I would interpret as "attitude".


Regards, TMS

I didn't mean to be rude or anything, It's just this workbook project that I have to finish ASAP ("annoying boss"), and I'm Excel beginner. Seems that everything I do crashes or doesn't work, so I'm a little bit agitated once a while. My intentions weren't bad, I know how hard you guys here struggle to help others for free, and you all have my respect !!!...Now, enough about this...UDF, don't have a clue what this is, maybe that is the problem. I attached sample worksheet, my CF formula highlights whole week if sum of working hours exceeds 56. Cells which are highlighted are also formula-based, maybe that is problem, but It worked fine in previous workbook.

CF formula is allready set, but just in case, here it is:

Please Login or Register  to view this content.

In sample workbook I only defined range E2:AI2, but it actually needs to be working in every parallel rows - E10, E14,E18 and so on...

Also - I'm wondering If this CF formula is going to work in Excel 2003, we still have some machines at work with old software ?

the sumproduct is evaluating to #value as you are trying to to multiply cells by ""
try changing
=IFERROR(MOD(RIGHT(G4,5)-LEFT(G4,5),1)*24,"")
to
=IFERROR(MOD(RIGHT(G4,5)-LEFT(G4,5),1)*24,0) also i doesn't need to be array entered
also once i did that i tested in excel 97 and the row highlights red up to S5
you can custom format the cell not to show 0 use a custom format
Select the cells that contain the zero (0) values that you want to hide.
On the Format menu, click Cells, and then click the Number tab.
In the Category list, click Custom.
In the Type box, type 0;-0;;@
but!!!!!!!
IFERROR WILL NOT WORK PRE 2007
so i changed the formula to
=IF(ISERROR(MOD(RIGHT(F4,5)-LEFT(F4,5),1)*24),0,MOD(RIGHT(F4,5)-LEFT(F4,5),1)*24)

here is a workbook done in excel 97

You will also need to change the conditional formatting formula from:

Formula:

Please Login or Register  to view this content.

to

Formula:

Please Login or Register  to view this content.

To be able to copy the cell formula and the CF down.


Regards, TMS

I don't see any change in these two formulas

Look again.

You'd have to copy the cells in row 5 (E5:AI5) and paste the formulas/formatting into row 10, 14, etc.

Regards, TMS