|
How do I determine if conditional formatting is applied to an exc.
How do I determine if conditional formatting is applied to a spreadsheet cell
in Excel. I tried the Font Object and the Interior object, but they both apply to normal conditions. |
How do I determine if conditional formatting is applied to an exc.
Start with this kind of logic. If the count is 0 then no CF
Sub CondCount() fcnt = Selection.FormatConditions.Count MsgBox fcnt End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... How do I determine if conditional formatting is applied to a spreadsheet cell in Excel. I tried the Font Object and the Interior object, but they both apply to normal conditions. |
How do I determine if conditional formatting is applied to an
This tells if there is a condition, but unless I read Mark's original post
wrong I think he wants to know if the format is applied; i.e. active. That turns out to be a difficult issue! There is no property that tells you quickly if a format condition is met or no, at least none I know of. The best we have is the formula in FormatConditions(n).Formula1. But that is a string and would need to be processed to turn it into VBA code to evaluate - yikes. So then I thought, why not temporarily put the formula from the conditional formatting into the cell formula and use the worksheet calculations to test the result (I would store the original cell formula and then paste it back in the cell when done). I found first that the formula text when read is applied to the ACTIVE cell, even though you are reading the property for a specified cell - so relative references will be a problem unless you first activate the cell you want to test. I could get the test to work, but ran into one problem: if the conditional format is self-referential (i.e. if it looks at the cell it is being applied to) I end up with a circular reference when I replace the cell formula with the conditional formula. I don't know any easy solution to this - the best I can think is to parse that conditional formula but that would be an incredibly difficult task... Anyone know any way around this? "Ken Wright" wrote: Start with this kind of logic. If the count is 0 then no CF Sub CondCount() fcnt = Selection.FormatConditions.Count MsgBox fcnt End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... How do I determine if conditional formatting is applied to a spreadsheet cell in Excel. I tried the Font Object and the Interior object, but they both apply to normal conditions. |
How do I determine if conditional formatting is applied to an
Chip Pearson shows a way:
http://cpearson.com/excel/CFColors.htm K Dales wrote: This tells if there is a condition, but unless I read Mark's original post wrong I think he wants to know if the format is applied; i.e. active. That turns out to be a difficult issue! There is no property that tells you quickly if a format condition is met or no, at least none I know of. The best we have is the formula in FormatConditions(n).Formula1. But that is a string and would need to be processed to turn it into VBA code to evaluate - yikes. So then I thought, why not temporarily put the formula from the conditional formatting into the cell formula and use the worksheet calculations to test the result (I would store the original cell formula and then paste it back in the cell when done). I found first that the formula text when read is applied to the ACTIVE cell, even though you are reading the property for a specified cell - so relative references will be a problem unless you first activate the cell you want to test. I could get the test to work, but ran into one problem: if the conditional format is self-referential (i.e. if it looks at the cell it is being applied to) I end up with a circular reference when I replace the cell formula with the conditional formula. I don't know any easy solution to this - the best I can think is to parse that conditional formula but that would be an incredibly difficult task... Anyone know any way around this? "Ken Wright" wrote: Start with this kind of logic. If the count is 0 then no CF Sub CondCount() fcnt = Selection.FormatConditions.Count MsgBox fcnt End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... How do I determine if conditional formatting is applied to a spreadsheet cell in Excel. I tried the Font Object and the Interior object, but they both apply to normal conditions. -- Dave Peterson |
How do I determine if conditional formatting is applied to an
Thanks - helpful, but still has a limitation (conditions must use absolute
cell references). Still would like to find a way that does not place any limitation on the type of condition allowed. "Dave Peterson" wrote: Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm K Dales wrote: This tells if there is a condition, but unless I read Mark's original post wrong I think he wants to know if the format is applied; i.e. active. That turns out to be a difficult issue! There is no property that tells you quickly if a format condition is met or no, at least none I know of. The best we have is the formula in FormatConditions(n).Formula1. But that is a string and would need to be processed to turn it into VBA code to evaluate - yikes. So then I thought, why not temporarily put the formula from the conditional formatting into the cell formula and use the worksheet calculations to test the result (I would store the original cell formula and then paste it back in the cell when done). I found first that the formula text when read is applied to the ACTIVE cell, even though you are reading the property for a specified cell - so relative references will be a problem unless you first activate the cell you want to test. I could get the test to work, but ran into one problem: if the conditional format is self-referential (i.e. if it looks at the cell it is being applied to) I end up with a circular reference when I replace the cell formula with the conditional formula. I don't know any easy solution to this - the best I can think is to parse that conditional formula but that would be an incredibly difficult task... Anyone know any way around this? "Ken Wright" wrote: Start with this kind of logic. If the count is 0 then no CF Sub CondCount() fcnt = Selection.FormatConditions.Count MsgBox fcnt End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... How do I determine if conditional formatting is applied to a spreadsheet cell in Excel. I tried the Font Object and the Interior object, but they both apply to normal conditions. -- Dave Peterson |
How do I determine if conditional formatting is applied to an
There is another problem with the solution at
http://cpearson.com/excel/CFColors.htm There is an undefined function in the demo code. GetStrippedValue(cellReference as Range) Makes it really hard to try out a 'solution' if something is missing. Plus it looks like the GetStrippedValue might be a formidable function. "K Dales" wrote: Thanks - helpful, but still has a limitation (conditions must use absolute cell references). Still would like to find a way that does not place any limitation on the type of condition allowed. "Dave Peterson" wrote: Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm K Dales wrote: This tells if there is a condition, but unless I read Mark's original post wrong I think he wants to know if the format is applied; i.e. active. That turns out to be a difficult issue! There is no property that tells you quickly if a format condition is met or no, at least none I know of. The best we have is the formula in FormatConditions(n).Formula1. But that is a string and would need to be processed to turn it into VBA code to evaluate - yikes. So then I thought, why not temporarily put the formula from the conditional formatting into the cell formula and use the worksheet calculations to test the result (I would store the original cell formula and then paste it back in the cell when done). I found first that the formula text when read is applied to the ACTIVE cell, even though you are reading the property for a specified cell - so relative references will be a problem unless you first activate the cell you want to test. I could get the test to work, but ran into one problem: if the conditional format is self-referential (i.e. if it looks at the cell it is being applied to) I end up with a circular reference when I replace the cell formula with the conditional formula. I don't know any easy solution to this - the best I can think is to parse that conditional formula but that would be an incredibly difficult task... Anyone know any way around this? "Ken Wright" wrote: Start with this kind of logic. If the count is 0 then no CF Sub CondCount() fcnt = Selection.FormatConditions.Count MsgBox fcnt End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... How do I determine if conditional formatting is applied to a spreadsheet cell in Excel. I tried the Font Object and the Interior object, but they both apply to normal conditions. -- Dave Peterson |
How do I determine if conditional formatting is applied to an
There is an undefined function in the demo code.
GetStrippedValue(cellReference as Range) Makes it really hard to try out a 'solution' if something is missing. Makes it really hard to give a solution if people don't read all the text on the page, especially the opening paragraph :-) ActiveCondition This function will return the number of the condition that is currently applied to the cell. If the cell does not have any conditional formatting defined, or none of the conditional formats are currently applied, it returns 0. Otherwise, it returns 1, 2, or 3, indicating with format condition is in effect. ActiveCondition requires the GetStrippedValue function at the bottom of this page<<< -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... There is another problem with the solution at http://cpearson.com/excel/CFColors.htm There is an undefined function in the demo code. GetStrippedValue(cellReference as Range) Makes it really hard to try out a 'solution' if something is missing. Plus it looks like the GetStrippedValue might be a formidable function. "K Dales" wrote: Thanks - helpful, but still has a limitation (conditions must use absolute cell references). Still would like to find a way that does not place any limitation on the type of condition allowed. "Dave Peterson" wrote: Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm K Dales wrote: This tells if there is a condition, but unless I read Mark's original post wrong I think he wants to know if the format is applied; i.e. active. That turns out to be a difficult issue! There is no property that tells you quickly if a format condition is met or no, at least none I know of. The best we have is the formula in FormatConditions(n).Formula1. But that is a string and would need to be processed to turn it into VBA code to evaluate - yikes. So then I thought, why not temporarily put the formula from the conditional formatting into the cell formula and use the worksheet calculations to test the result (I would store the original cell formula and then paste it back in the cell when done). I found first that the formula text when read is applied to the ACTIVE cell, even though you are reading the property for a specified cell - so relative references will be a problem unless you first activate the cell you want to test. I could get the test to work, but ran into one problem: if the conditional format is self-referential (i.e. if it looks at the cell it is being applied to) I end up with a circular reference when I replace the cell formula with the conditional formula. I don't know any easy solution to this - the best I can think is to parse that conditional formula but that would be an incredibly difficult task... Anyone know any way around this? "Ken Wright" wrote: Start with this kind of logic. If the count is 0 then no CF Sub CondCount() fcnt = Selection.FormatConditions.Count MsgBox fcnt End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... How do I determine if conditional formatting is applied to a spreadsheet cell in Excel. I tried the Font Object and the Interior object, but they both apply to normal conditions. -- Dave Peterson |
How do I determine if conditional formatting is applied to an
Ok, you caught me there :@ I was looking around and I think I might have
found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark "Ken Wright" wrote: There is an undefined function in the demo code. GetStrippedValue(cellReference as Range) Makes it really hard to try out a 'solution' if something is missing. Makes it really hard to give a solution if people don't read all the text on the page, especially the opening paragraph :-) ActiveCondition This function will return the number of the condition that is currently applied to the cell. If the cell does not have any conditional formatting defined, or none of the conditional formats are currently applied, it returns 0. Otherwise, it returns 1, 2, or 3, indicating with format condition is in effect. ActiveCondition requires the GetStrippedValue function at the bottom of this page<<< -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... There is another problem with the solution at http://cpearson.com/excel/CFColors.htm There is an undefined function in the demo code. GetStrippedValue(cellReference as Range) Makes it really hard to try out a 'solution' if something is missing. Plus it looks like the GetStrippedValue might be a formidable function. "K Dales" wrote: Thanks - helpful, but still has a limitation (conditions must use absolute cell references). Still would like to find a way that does not place any limitation on the type of condition allowed. "Dave Peterson" wrote: Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm K Dales wrote: This tells if there is a condition, but unless I read Mark's original post wrong I think he wants to know if the format is applied; i.e. active. That turns out to be a difficult issue! There is no property that tells you quickly if a format condition is met or no, at least none I know of. The best we have is the formula in FormatConditions(n).Formula1. But that is a string and would need to be processed to turn it into VBA code to evaluate - yikes. So then I thought, why not temporarily put the formula from the conditional formatting into the cell formula and use the worksheet calculations to test the result (I would store the original cell formula and then paste it back in the cell when done). I found first that the formula text when read is applied to the ACTIVE cell, even though you are reading the property for a specified cell - so relative references will be a problem unless you first activate the cell you want to test. I could get the test to work, but ran into one problem: if the conditional format is self-referential (i.e. if it looks at the cell it is being applied to) I end up with a circular reference when I replace the cell formula with the conditional formula. I don't know any easy solution to this - the best I can think is to parse that conditional formula but that would be an incredibly difficult task... Anyone know any way around this? "Ken Wright" wrote: Start with this kind of logic. If the count is 0 then no CF Sub CondCount() fcnt = Selection.FormatConditions.Count MsgBox fcnt End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... How do I determine if conditional formatting is applied to a spreadsheet cell in Excel. I tried the Font Object and the Interior object, but they both apply to normal conditions. -- Dave Peterson |
How do I determine if conditional formatting is applied to an
LOL :-)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip |
How do I determine if conditional formatting is applied to an
Well it looks like there is no way of programatically determining if
conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip |
How do I determine if conditional formatting is applied to an
Did you look at Chip Pearson's site (previously mentioned in this thread)?
Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
How do I determine if conditional formatting is applied to an
Yes, I did look at the Chip Pearson site. I have finished my VBA code for
determining if ConditionalFormatting is applied. You can view the results at: http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any questions or comments you can post them to this message. "Dave Peterson" wrote: Did you look at Chip Pearson's site (previously mentioned in this thread)? Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
How do I determine if conditional formatting is applied to an
A few people have tried to point out to you why you were not succeeding with
Chip's function. And that despite the clear instructions given by Chip. I'll have a go as well because, having just read your link to your page, I was amazed to see your comment: "I was suprised that the author [Chip] did not use the Evaluate Method" I've seen various functions relating to Format Conditions, and written one of my own (some different features to anything I've seen). They all use Evaluate, including of course Chip's. Could I suggest as others have that you carefully read Chip's page in full, together with previous advice given in this thread. Re-read repeatedly until the light dawns. It will :-) Regards, Peter T "MarkTheNuke" wrote in message ... Yes, I did look at the Chip Pearson site. I have finished my VBA code for determining if ConditionalFormatting is applied. You can view the results at: http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any questions or comments you can post them to this message. "Dave Peterson" wrote: Did you look at Chip Pearson's site (previously mentioned in this thread)? Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
How do I determine if conditional formatting is applied to an
Yes, Chip does use Evaluate for the expression type of format conditions,
however, if you play around with conditional formatting, you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine. Like I said, I was looking for a specific result, which is why I wrote my own spreadsheet. I did not mean to disparage Chips effort, if I did then I apologize. I just made a comment about something I thought was missing. I did read Chips routines, more than once, and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied, actually I was looking if it was applied, but there is no function that determines that, maybe you misread my original question, or I was not clear enough. "Peter T" wrote: A few people have tried to point out to you why you were not succeeding with Chip's function. And that despite the clear instructions given by Chip. I'll have a go as well because, having just read your link to your page, I was amazed to see your comment: "I was suprised that the author [Chip] did not use the Evaluate Method" I've seen various functions relating to Format Conditions, and written one of my own (some different features to anything I've seen). They all use Evaluate, including of course Chip's. Could I suggest as others have that you carefully read Chip's page in full, together with previous advice given in this thread. Re-read repeatedly until the light dawns. It will :-) Regards, Peter T "MarkTheNuke" wrote in message ... Yes, I did look at the Chip Pearson site. I have finished my VBA code for determining if ConditionalFormatting is applied. You can view the results at: http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any questions or comments you can post them to this message. "Dave Peterson" wrote: Did you look at Chip Pearson's site (previously mentioned in this thread)? Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
How do I determine if conditional formatting is applied to an
Comments in line:
maybe you misread my original question, or I was not clear Could be either / or. Let's find out - and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied Assuming you are looking at single cell A1 you would enter =ActiveCondition(A1) This will return 0 (no conditions are true) or 1 to 3 to indicate which of the three conditions are true. Doesn't that fullfill the requirement? actually I was looking if it was applied, Not quite sure what you mean here unless you want to return the ColorIndex of Fill or Font that been applied relates to a true condition, or the underflying index if no conditions are true (btw - a function very adaptable to own needs) =ColorIndexOfCF(A1,false) Have you put all Chip's functions, including the required helper GetStrippedValue, into a module and tried each. What have I missed? Regards, Peter T "MarkTheNuke" wrote in message ... Yes, Chip does use Evaluate for the expression type of format conditions, however, if you play around with conditional formatting, you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine. Like I said, I was looking for a specific result, which is why I wrote my own spreadsheet. I did not mean to disparage Chips effort, if I did then I apologize. I just made a comment about something I thought was missing. I did read Chips routines, more than once, and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied, actually I was looking if it was applied, but there is no function that determines that, maybe you misread my original question, or I was not clear enough. "Peter T" wrote: A few people have tried to point out to you why you were not succeeding with Chip's function. And that despite the clear instructions given by Chip. I'll have a go as well because, having just read your link to your page, I was amazed to see your comment: "I was suprised that the author [Chip] did not use the Evaluate Method" I've seen various functions relating to Format Conditions, and written one of my own (some different features to anything I've seen). They all use Evaluate, including of course Chip's. Could I suggest as others have that you carefully read Chip's page in full, together with previous advice given in this thread. Re-read repeatedly until the light dawns. It will :-) Regards, Peter T "MarkTheNuke" wrote in message ... Yes, I did look at the Chip Pearson site. I have finished my VBA code for determining if ConditionalFormatting is applied. You can view the results at: http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any questions or comments you can post them to this message. "Dave Peterson" wrote: Did you look at Chip Pearson's site (previously mentioned in this thread)? Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
How do I determine if conditional formatting is applied to an
Perhaps I did misread, quoted from from your previous post:
you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine I've never thought to do that - enter a formula to be compared. Quite unusual. In which case simple enough to amend Chip's functions. Along the lines that if the first character in the string is an "=" it's probably a formula that's best evaluated. In ActiveCondition, under Temp2 = GetStrippedValue(FC.Formula2) there's an if/else. Add an extra Elseif to check that "=" and Evaluate. Would also need a bit more checking. Regards, Peter T. "Peter T" <peter_t@discussions wrote in message ... Comments in line: maybe you misread my original question, or I was not clear Could be either / or. Let's find out - and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied Assuming you are looking at single cell A1 you would enter =ActiveCondition(A1) This will return 0 (no conditions are true) or 1 to 3 to indicate which of the three conditions are true. Doesn't that fullfill the requirement? actually I was looking if it was applied, Not quite sure what you mean here unless you want to return the ColorIndex of Fill or Font that been applied relates to a true condition, or the underflying index if no conditions are true (btw - a function very adaptable to own needs) =ColorIndexOfCF(A1,false) Have you put all Chip's functions, including the required helper GetStrippedValue, into a module and tried each. What have I missed? Regards, Peter T "MarkTheNuke" wrote in message ... Yes, Chip does use Evaluate for the expression type of format conditions, however, if you play around with conditional formatting, you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine. Like I said, I was looking for a specific result, which is why I wrote my own spreadsheet. I did not mean to disparage Chips effort, if I did then I apologize. I just made a comment about something I thought was missing. I did read Chips routines, more than once, and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied, actually I was looking if it was applied, but there is no function that determines that, maybe you misread my original question, or I was not clear enough. "Peter T" wrote: A few people have tried to point out to you why you were not succeeding with Chip's function. And that despite the clear instructions given by Chip. I'll have a go as well because, having just read your link to your page, I was amazed to see your comment: "I was suprised that the author [Chip] did not use the Evaluate Method" I've seen various functions relating to Format Conditions, and written one of my own (some different features to anything I've seen). They all use Evaluate, including of course Chip's. Could I suggest as others have that you carefully read Chip's page in full, together with previous advice given in this thread. Re-read repeatedly until the light dawns. It will :-) Regards, Peter T "MarkTheNuke" wrote in message ... Yes, I did look at the Chip Pearson site. I have finished my VBA code for determining if ConditionalFormatting is applied. You can view the results at: http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any questions or comments you can post them to this message. "Dave Peterson" wrote: Did you look at Chip Pearson's site (previously mentioned in this thread)? Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
How do I determine if conditional formatting is applied to an
Perhaps I did misread, quoted from from your previous post:
you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine I've never thought to do that - enter a formula to be compared. Quite unusual. In which case simple enough to amend Chip's functions. Along the lines that if the first character in the string is an "=" it's probably a formula that's best evaluated. In ActiveCondition, under Temp2 = GetStrippedValue(FC.Formula2) there's an if/else. Add an extra Elseif to check that "=" and Evaluate. Would also need a bit more checking. Regards, Peter T. "Peter T" <peter_t@discussions wrote in message ... Comments in line: maybe you misread my original question, or I was not clear Could be either / or. Let's find out - and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied Assuming you are looking at single cell A1 you would enter =ActiveCondition(A1) This will return 0 (no conditions are true) or 1 to 3 to indicate which of the three conditions are true. Doesn't that fullfill the requirement? actually I was looking if it was applied, Not quite sure what you mean here unless you want to return the ColorIndex of Fill or Font that been applied relates to a true condition, or the underflying index if no conditions are true (btw - a function very adaptable to own needs) =ColorIndexOfCF(A1,false) Have you put all Chip's functions, including the required helper GetStrippedValue, into a module and tried each. What have I missed? Regards, Peter T "MarkTheNuke" wrote in message ... Yes, Chip does use Evaluate for the expression type of format conditions, however, if you play around with conditional formatting, you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine. Like I said, I was looking for a specific result, which is why I wrote my own spreadsheet. I did not mean to disparage Chips effort, if I did then I apologize. I just made a comment about something I thought was missing. I did read Chips routines, more than once, and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied, actually I was looking if it was applied, but there is no function that determines that, maybe you misread my original question, or I was not clear enough. "Peter T" wrote: A few people have tried to point out to you why you were not succeeding with Chip's function. And that despite the clear instructions given by Chip. I'll have a go as well because, having just read your link to your page, I was amazed to see your comment: "I was suprised that the author [Chip] did not use the Evaluate Method" I've seen various functions relating to Format Conditions, and written one of my own (some different features to anything I've seen). They all use Evaluate, including of course Chip's. Could I suggest as others have that you carefully read Chip's page in full, together with previous advice given in this thread. Re-read repeatedly until the light dawns. It will :-) Regards, Peter T "MarkTheNuke" wrote in message ... Yes, I did look at the Chip Pearson site. I have finished my VBA code for determining if ConditionalFormatting is applied. You can view the results at: http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any questions or comments you can post them to this message. "Dave Peterson" wrote: Did you look at Chip Pearson's site (previously mentioned in this thread)? Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
How do I determine if conditional formatting is applied to an
You don't even have to enter a formula, if you enter a cell reference it will
fail because it performs a string comparision on the cell address instead of getting the value of the referenced cell. If you use the Evaluate Method, you have to use the worksheet object as the calling reference, since if you do use cell addresses they are not resolved to their correct worksheet. You can't enter Sheet2!$A$1 as a conditional value, but you can enter $A$1. Like I said, a lot of what I did was refactor the code that Chip provided. Also I did paste Chip's code into an excel spreadsheet, unfortunately it did fail when I used my first address. "Peter T" wrote: Perhaps I did misread, quoted from from your previous post: you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine I've never thought to do that - enter a formula to be compared. Quite unusual. In which case simple enough to amend Chip's functions. Along the lines that if the first character in the string is an "=" it's probably a formula that's best evaluated. In ActiveCondition, under Temp2 = GetStrippedValue(FC.Formula2) there's an if/else. Add an extra Elseif to check that "=" and Evaluate. Would also need a bit more checking. Regards, Peter T. "Peter T" <peter_t@discussions wrote in message ... Comments in line: maybe you misread my original question, or I was not clear Could be either / or. Let's find out - and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied Assuming you are looking at single cell A1 you would enter =ActiveCondition(A1) This will return 0 (no conditions are true) or 1 to 3 to indicate which of the three conditions are true. Doesn't that fullfill the requirement? actually I was looking if it was applied, Not quite sure what you mean here unless you want to return the ColorIndex of Fill or Font that been applied relates to a true condition, or the underflying index if no conditions are true (btw - a function very adaptable to own needs) =ColorIndexOfCF(A1,false) Have you put all Chip's functions, including the required helper GetStrippedValue, into a module and tried each. What have I missed? Regards, Peter T "MarkTheNuke" wrote in message ... Yes, Chip does use Evaluate for the expression type of format conditions, however, if you play around with conditional formatting, you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine. Like I said, I was looking for a specific result, which is why I wrote my own spreadsheet. I did not mean to disparage Chips effort, if I did then I apologize. I just made a comment about something I thought was missing. I did read Chips routines, more than once, and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied, actually I was looking if it was applied, but there is no function that determines that, maybe you misread my original question, or I was not clear enough. "Peter T" wrote: A few people have tried to point out to you why you were not succeeding with Chip's function. And that despite the clear instructions given by Chip. I'll have a go as well because, having just read your link to your page, I was amazed to see your comment: "I was suprised that the author [Chip] did not use the Evaluate Method" I've seen various functions relating to Format Conditions, and written one of my own (some different features to anything I've seen). They all use Evaluate, including of course Chip's. Could I suggest as others have that you carefully read Chip's page in full, together with previous advice given in this thread. Re-read repeatedly until the light dawns. It will :-) Regards, Peter T "MarkTheNuke" wrote in message ... Yes, I did look at the Chip Pearson site. I have finished my VBA code for determining if ConditionalFormatting is applied. You can view the results at: http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any questions or comments you can post them to this message. "Dave Peterson" wrote: Did you look at Chip Pearson's site (previously mentioned in this thread)? Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
How do I determine if conditional formatting is applied to an
OK I see your point. In haste to concede "I misread" and beat you to it, I
didn't take a close look at all the implications. I have since though looked at my own function (I mentioned earlier) that does cater for the scenario you refer to. FWIW, a tiny snippet: For each cell in rng For each fc in cell.formatconditions ' some other stuff vF1 = Evaluate(fc.Formula1) If fc.Type = xlExpression Then bln = vF1 Else If fc.Operator < 3 Then vF2 = Evaluate(fc.Formula2) Select Case fc.Operator ' etc bln = true if relevant condition met in case's why < 3 (xlEqual), cos only then have Formula2 It's well over a year since I wrote this and forgot all the intricacies. Apart from the obvious that's in common to all similar functions, mine is very different from Chip's or any other I've seen. It's not though a UDF and as such has the luxury to cater for other problems, such as relative vs absolute addressing in multicell ranges. In your post that I first replied to, you did clearly stated the problem. As I've already said I misread, or rather didn't twig. That's despite having spent some considerable time on a similar function. In mitigation, I recalled reading your earlier posts in which you had not described the problem. Namely, in particular respect to the formula in comparison vs expression. Very difficult for most readers to have picked up on, even those who have worked with format conditions. You very much gave the impression of not having read Chip's instructions, eg " There is an undefined function in the demo code. GetStrippedValue(cellReference as Range) Makes it really hard to try out a 'solution' if something is missing. " Hence the frustrating dialog that ensued! Anyway, got there in the end. And wouldn't take much to have adapted Chip's for your requirements. Regards, Peter T "MarkTheNuke" wrote in message ... You don't even have to enter a formula, if you enter a cell reference it will fail because it performs a string comparision on the cell address instead of getting the value of the referenced cell. If you use the Evaluate Method, you have to use the worksheet object as the calling reference, since if you do use cell addresses they are not resolved to their correct worksheet. You can't enter Sheet2!$A$1 as a conditional value, but you can enter $A$1. Like I said, a lot of what I did was refactor the code that Chip provided. Also I did paste Chip's code into an excel spreadsheet, unfortunately it did fail when I used my first address. "Peter T" wrote: Perhaps I did misread, quoted from from your previous post: you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine I've never thought to do that - enter a formula to be compared. Quite unusual. In which case simple enough to amend Chip's functions. Along the lines that if the first character in the string is an "=" it's probably a formula that's best evaluated. In ActiveCondition, under Temp2 = GetStrippedValue(FC.Formula2) there's an if/else. Add an extra Elseif to check that "=" and Evaluate. Would also need a bit more checking. Regards, Peter T. "Peter T" <peter_t@discussions wrote in message ... Comments in line: maybe you misread my original question, or I was not clear Could be either / or. Let's find out - and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied Assuming you are looking at single cell A1 you would enter =ActiveCondition(A1) This will return 0 (no conditions are true) or 1 to 3 to indicate which of the three conditions are true. Doesn't that fullfill the requirement? actually I was looking if it was applied, Not quite sure what you mean here unless you want to return the ColorIndex of Fill or Font that been applied relates to a true condition, or the underflying index if no conditions are true (btw - a function very adaptable to own needs) =ColorIndexOfCF(A1,false) Have you put all Chip's functions, including the required helper GetStrippedValue, into a module and tried each. What have I missed? Regards, Peter T "MarkTheNuke" wrote in message ... Yes, Chip does use Evaluate for the expression type of format conditions, however, if you play around with conditional formatting, you can enter a formula into the value comparison routines, which means you would have to use the Evaluate Method for the value comparison routine. Like I said, I was looking for a specific result, which is why I wrote my own spreadsheet. I did not mean to disparage Chips effort, if I did then I apologize. I just made a comment about something I thought was missing. I did read Chips routines, more than once, and like I said the big thing I was looking for was whether ConditionalFormatting should have been applied, actually I was looking if it was applied, but there is no function that determines that, maybe you misread my original question, or I was not clear enough. "Peter T" wrote: A few people have tried to point out to you why you were not succeeding with Chip's function. And that despite the clear instructions given by Chip. I'll have a go as well because, having just read your link to your page, I was amazed to see your comment: "I was suprised that the author [Chip] did not use the Evaluate Method" I've seen various functions relating to Format Conditions, and written one of my own (some different features to anything I've seen). They all use Evaluate, including of course Chip's. Could I suggest as others have that you carefully read Chip's page in full, together with previous advice given in this thread. Re-read repeatedly until the light dawns. It will :-) Regards, Peter T "MarkTheNuke" wrote in message ... Yes, I did look at the Chip Pearson site. I have finished my VBA code for determining if ConditionalFormatting is applied. You can view the results at: http://home.comcast.net/~mgillesp/Co...Formatting.htm, if you have any questions or comments you can post them to this message. "Dave Peterson" wrote: Did you look at Chip Pearson's site (previously mentioned in this thread)? Chip Pearson shows a way: http://cpearson.com/excel/CFColors.htm MarkTheNuke wrote: Well it looks like there is no way of programatically determining if conditional formatting is applied, the best to be hoped for is determining if conditional formatting should be applied based on the contents of the cell and the conditional formatting conditions. I have it all in a spreadsheet, I will provide a link once I get it onto my web pages. (In about 2 years) :) "Ken Wright" wrote: LOL :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "MarkTheNuke" wrote in message ... Ok, you caught me there :@ I was looking around and I think I might have found a cleaner solution using the Evaluate Method. However, it still does not answer my question about determined if Conditional Formatting is applied, I guess the answer is you can't, except by visual examination. Mark <snip -- Dave Peterson |
| All times are GMT +1. The time now is 08:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com