• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Format Formula to Match Another Cells Format

dparteka

dparteka

Member
Is it possible to put a formula in a conditional format that causes a match of the format in a different cell?

Let’s say we can enter a number 1 or 2 in cell A1… the conditional formatting in A1 causes white fill for 1 and red fill for 2. The conditional format formula in B2 would cause a match of the fill color that is in A1.

NOTE: In the real world A1 is not limited to just the numbers 1 and 2 and could be any number… simply put, whatever fill color is in A1 would be the same in B1.
 
It is very difficult to access the Conditional Formatting colors of a cell

The normal technique is simply to use the same logic as the CF cells in the new cells
 
Thank-you Hui… I suspected that would be the case. I can’t use the same logic in both cells however there is an alternative solution that would work but it’s beyond my skill level, maybe you can help or maybe I’m trying to do something that’s not possible, I’d need a CF formula in A5 that can do the following…

If any part of the text “Angle” are found in A1… and A5 is not between A2 and A3

OR... If any part of the text “Surface” are found in A1… and A5 is not equal to A3/40

OR... If any other texts are or are not found in A1… and A5 is not between the A2*25.4 and A3*25.4 then red fill

I’m hoping that something on the line of OR, IF, ISNUMBER, SEARCH can be used, what do you think; is this way out there?

Thanks… Dennis
 
Hi Dennis ,

The first two rules can be taken care of by a formula on the lines of the following :

=IF(OR(AND(ISNUMBER(SEARCH("Angle",A1)),A5<>"",OR(A5<A2,A5>A3)),AND(ISNUMBER(SEARCH("Surface",A1)),A5<>"",A5<>A3/40)),1,0)

The third is somewhat confusing. Can you explain in more detail , with specific references to values and cells ?

Narayan
 
Narayan, good to here from you

If the first two rules do not apply (that being A1 does not have "Angle" or "Surface" in it) then the last rule would apply... does that help?

Dennis
 
Hi Dennis ,

See if this is correct :

=IF(OR(AND(ISNUMBER(SEARCH("Angle",A1)),A5<>"",OR(A5<A2,A5>A3)),AND(ISNUMBER(SEARCH("Surface",A1)),A5<>"",A5<>A3/40),AND(ISERR(SEARCH("Angle",A1)),ISERR(SEARCH("Surface",A1)),A5<>"",OR(A5<A2*25.4,A5>A3*25.4))),1,0)

where the part in PINK is the added portion.

Narayan
 
Good morning Narayan,

I have attached the spreadsheet and applied your CF formula to the U:V column, U8 is an example of the first rule, U28 is the second and U10 & U14 are the third. None of these should be red filled, I may have explained the goal incorrectly, and in addition I found an error in what I asked for in the 2nd rule… I’m hoping that now that you have the actual file that this will help with the explanation.

U8, the 1st rule… U8 should be red filled only if the text “Angle” appears in F8 AND if U8 does not fall between Q8 & R8

U28, the 2nd rule… U28 should be red filled only if the text “Surface” appears in F28 AND if U28 is greater than R28/40

U10, the 3rd rule… U10 should be red filled only if the 1st and 2nd rules do not apply AND U10 is not between Q10*25.4 and R10*25.4

U10 & U14 are both examples of the 3rd rule… I’m not sure why U14 works correctly and U10 does not

Best regards... Dennis
 

Attachments

  • CF Formula.xls
    837 KB · Views: 2
Hi Dennis ,

Still confusing. See the attached file , where your first 2 rules have been entered as CF formulae ; first see if these 2 work correctly.

Thereafter , see the formulae in AM10 and AM14 ; these are the same formula copied from AM10 to AM14 ; you can see that they are returning two different results. I have no idea how you say that both are examples of the same rule ; the same formula applied to U10 , Q10 , R10 and U14 , Q14 , R14 is not giving the same result.

Narayan
 

Attachments

  • CF Formula.xls
    807.5 KB · Views: 2
Narayan,

We are close, the “Surface” formula works perfectly as demonstrated in rows 26 thru 28.

If I change U7<Q7,U7>R7 to U7>Q7,U7<R7 the “Angle” formula also works perfectly as demonstrated in rows 8 thru 10. So the only thing left is to integrate the 3rd rule that says if the 1st and 2nd rules do not apply AND U1 is not between Q1*25.4 and R1*25.4

As for your comment related to the formulas in AM10 & AM14, I’m not completely positive what you are pointing out here however could it be this? Because F10 has “Angle” in it and F14 does not, this means that there is a *25.4 calculation done in AM14 but not in AM10. In any case everything is working as intended except for the missing 3rd rule.

Dennis
 

Attachments

  • CF Formula.xls
    803.5 KB · Views: 0
Hi Dennis ,

Can you clarify the meaning of the following ?

Rules 1 and 2 do not apply

Each of these rules is a combination of 4 tests :

1. The cell in column F contains a specified word Angle in one case , and Surface in the other

2. The cell in column U is not blank

3. The value in the cell in column U fulfills a specified rule.

If you say Rule 1 and Rule 2 does not apply , it can be for any of the following reasons :

1. The cell in column F contains text other than Angle and Surface

2. The cell in column U is blank

3. The value in the cell in column U does not fulfill the specified rule

Here , you can have 1 alone , or 2 alone , or 3 alone , or 1 and 2 together , 1 and 3 together.

Which exactly is it ? Under which of these circumstances should rule 3 be applied ?

Narayan
 
Narayan,

If I'm understanding this correctly I'd say 1 and 3 together, that is if this means the formula will look for Angle, then when not found will look for Surface, then when not found will red fill the cell if the number in U does not fall within the range of Q*25.4 - R*25.4.

Another consideration, the last CF condition on the spreadsheet is ="Reject", maybe this condition could possibly be combined with the third rule, then 1 & 2 could be left alone as they are... I'm just throwing that out here, I really have no idea if this is a good idea or not.

Dennis
 
Hi Dennis ,

Does that mean if a cell in column F does not have Angle or Surface , then somewhere else in that row , a cell will have Reject in it ?

Please note that CF formula do not work in sequence ; they are tested in the order in which they appear , and if the Stop If True checkbox is not checked , all of them will be tested and the last one to be found true will be applied.

For example , suppose we have a rule :

=A1 > 5

then cell A1 is colored RED.

We have a second rule :

=A1 > 7

then cell A1 is colored GREEN.

So when A1 equals 8 , it will satisfy both conditions , and which rule will be applied depends on whether the Stop If True checkbox of the first rule is checked or not , and the order in which these rules have been added.

Your first two rules are mutually exclusive because one is checking for one word , while the other checks for a different word.

See if this is correct.

Narayan
 

Attachments

  • CF Formula.xlsm
    400.6 KB · Views: 2
Narayan,

Sorry I should have told you... the text "Reject" is manually typed in column S and only appears in S & U, it never appears in F... see the attached file

Dennis
 

Attachments

  • CF Formula.xlsm
    318 KB · Views: 1
Hi Dennis ,

The problem was a missing parenthesis ! See this file and comment.

Narayan
 

Attachments

  • CF Formula (1).xlsm
    320.6 KB · Views: 3
Narayan,

Sorry for the delayed response, I’ve been try to figure out what’s going on at this end, here’s what I’ve discovered. If you can believe it I’m still on Excel 2003 so I’m not able to see all four of your CF’s; I will upgrade as soon as possible. I do currently have access to a PC with 2007 and was able to test all CF’s, you can see the results in the attached file. The problem that remains is pointed out in S14, U14, S16, U16. This is the result of what appears to me to be a rounding issue, if you look in the formula bar in cells Q14, R14, Q16, R16 you’ll see that the numbers in those cells which are generated from a formula in another spreadsheet far exceed the number of decimal places that are displayed… in addition if you type 8.2502 in Q14 (the upper limit) you’ll see that S14 turns white as it should however U14 does not…. same thing happens if you type 8.2498 in R16 (the low limit).

Dennis

Update: I've been doing more testing and discovered if I enter 8.25021 in Q14 and 8.2497 in R16 it all works as intended so I believe my only issue is the rounding of numbers, I'll work on resolving that however, if you have any suggestions I'd love to here them.
 

Attachments

  • CF Formulas.xlsm
    317.4 KB · Views: 1
Last edited:
Hi Dennis ,

Make use of this file for testing ; the 3 rules have been combined into 1 , so now there are only 2 rules , one which takes care of Reject , and the other which takes care of all the other conditions.

Narayan
 

Attachments

  • CF Formula (1).xlsm
    319 KB · Views: 1
Narayan,

I'm starting to feel like a nuisance, sorry about that. Please see U19 & U20 of the attached file... the combination of the four CL's did not produce is issue.

Dennis
 

Attachments

  • CF Formulas.xlsm
    317.5 KB · Views: 2
Hi Dennis ,

The problem is elsewhere ; in some rows , you have the smaller value in column R , and the bigger value in column Q ; in other rows , you have the smaller value in column Q and the bigger value in column R.

Narayan
 
Narayan,

Something came up and I had to break away from this project momentarily, I'll update you as soon as I can test your new CL formula.

Dennis
 
Narayan,

Halleluiah, I finally was able to test your CL formula… I’m blown away, that is some sophisticated Excel wizardry you did there… you are the Grand Master, the Picasso of Excel, it’s like intellectual art, very impressive… oh one other thing, it works perfectly.

Thank you, sincerely… Dennis
 
You must log in or register to reply here.
Back
Top