• 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 formatting is not working

G

GN0001

Member
Hello all,

I am doing a conditional formatting on a column and I want to use this option:
Use formula to determine which cells to format, this option doesn't work at all, (The other option: format only cells that contain works partially!).

Can anybody help me on this, please?

Thanks,
GN0001
 

Attachments

  • Book1.xlsx
    10.2 KB · Views: 12
Hi Guity!

But your CF is working perfect..

upload_2014-5-13_11-49-17.png

  • In your first CF.. you have asked excel to
If F7(one cell below.. from the starting location F6..) is greater than 4, then, apply RED Format to the F6..​
and just above the 1st 4, 3 is in RED.

  • In your 2nd CF, you have asked excel to
If F*(two cell below.. from the starting location F6..) is greater than 4, then, apply BLUE Format to the F6..​
and just 2 step above the 1st 4, 2 is in BLUE..

If you surprise.. then.. try to update CF Formula as..

upload_2014-5-13_11-49-47.png
 
Hi ,

Can you explain what you want your CF to do ?

The main thing to remember about CF is that there are two ways to apply CF to a range :

1. Put in the CF formula for one cell , which is the first / top-most cell in a range , using only relative references ; thus , if your range is F6:F17 , put in the CF for F6 , using F6 and not $F$6. The Applies To range always uses absolute referencing , so that does not matter. For example , if you want that a particular format should be applied to F6 if it is positive and non-zero , and another if it is negative , you can use the rules =F6>0 and =F6<0 , not $F$6>0 or $F$6<0.

After verifying that the CF rules are working correctly for this one cell , copy the format to the remaining cells in the range using either the Format Painter or using the Copy , Paste Special , Formats or even by extending the Applies To range from just =$F$6 to =$F$6:$F$17.

However , if you use the first two methods , then when you check the CF for the range , in F6 , you will find a rule which says :

=F6>0 , applies to =$F$6

while in F7 through F17 , you will see a rule which says :

=F7>0 , applies to =$F$7:$F$17

Note that the choice of F6 to enter the CF formula , and then copy the rule to F7 through F17 was arbitrary ; you can also choose to enter the first CF formula in any other cell in the range , say F11 , and then copy the resulting rule to cells F6 through F10 , and cells F12 through F17. However , if you do choose to do this , remember to use relative referencing , and enter =F11>0 as the rule for cell F11. Using this method will however make it more difficult to understand the CF rules , since the same rule will be copied to F6:F10 , and show =F6>0 there , while in the range F12:F17 , it will show =F12>0 !

------------------------------------------------------------------------------------------------------------------

2. The second way is to select the entire data range over which you want to apply the CF , and then open the CF dialog. In this case , again remember to use relative references , and use the first / top-most cell in the range as the reference point. Thus , in this case , select the range F6:F17 , and enter the formula : =F6>0.

In this case , when you see the CF for any cell in the range , you will see =F6>0 , and the Applies To range will be =$F$6:$F$17.

Narayan
 
Debraj said:
Hi Guity!

But your CF is working perfect..

View attachment 6553

  • In your first CF.. you have asked excel to
If F7(one cell below.. from the starting location F6..) is greater than 4, then, apply RED Format to the F6..​
and just above the 1st 4, 3 is in RED.

  • In your 2nd CF, you have asked excel to
If F*(two cell below.. from the starting location F6..) is greater than 4, then, apply BLUE Format to the F6..​
and just 2 step above the 1st 4, 2 is in BLUE..

If you surprise.. then.. try to update CF Formula as..

View attachment 6554
Click to expand...

Hello Debraj,

Please use my name as GN0001. I have been thinking on what you say. I am not even able to build what have rebuilt on the previous post.
NARAYANK991 said:
Hi ,

Can you explain what you want your CF to do ?

The main thing to remember about CF is that there are two ways to apply CF to a range :

1. Put in the CF formula for one cell , which is the first / top-most cell in a range , using only relative references ; thus , if your range is F6:F17 , put in the CF for F6 , using F6 and not $F$6. The Applies To range always uses absolute referencing , so that does not matter. For example , if you want that a particular format should be applied to F6 if it is positive and non-zero , and another if it is negative , you can use the rules =F6>0 and =F6<0 , not $F$6>0 or $F$6<0.

After verifying that the CF rules are working correctly for this one cell , copy the format to the remaining cells in the range using either the Format Painter or using the Copy , Paste Special , Formats or even by extending the Applies To range from just =$F$6 to =$F$6:$F$17.

However , if you use the first two methods , then when you check the CF for the range , in F6 , you will find a rule which says :

=F6>0 , applies to =$F$6

while in F7 through F17 , you will see a rule which says :

=F7>0 , applies to =$F$7:$F$17

Note that the choice of F6 to enter the CF formula , and then copy the rule to F7 through F17 was arbitrary ; you can also choose to enter the first CF formula in any other cell in the range , say F11 , and then copy the resulting rule to cells F6 through F10 , and cells F12 through F17. However , if you do choose to do this , remember to use relative referencing , and enter =F11>0 as the rule for cell F11. Using this method will however make it more difficult to understand the CF rules , since the same rule will be copied to F6:F10 , and show =F6>0 there , while in the range F12:F17 , it will show =F12>0 !

------------------------------------------------------------------------------------------------------------------

2. The second way is to select the entire data range over which you want to apply the CF , and then open the CF dialog. In this case , again remember to use relative references , and use the first / top-most cell in the range as the reference point. Thus , in this case , select the range F6:F17 , and enter the formula : =F6>0.

In this case , when you see the CF for any cell in the range , you will see =F6>0 , and the Applies To range will be =$F$6:$F$17.

Narayan
Click to expand...
NARAYANK991 said:
Hi ,

Can you explain what you want your CF to do ?

The main thing to remember about CF is that there are two ways to apply CF to a range :

1. Put in the CF formula for one cell , which is the first / top-most cell in a range , using only relative references ; thus , if your range is F6:F17 , put in the CF for F6 , using F6 and not $F$6. The Applies To range always uses absolute referencing , so that does not matter. For example , if you want that a particular format should be applied to F6 if it is positive and non-zero , and another if it is negative , you can use the rules =F6>0 and =F6<0 , not $F$6>0 or $F$6<0.

After verifying that the CF rules are working correctly for this one cell , copy the format to the remaining cells in the range using either the Format Painter or using the Copy , Paste Special , Formats or even by extending the Applies To range from just =$F$6 to =$F$6:$F$17.

However , if you use the first two methods , then when you check the CF for the range , in F6 , you will find a rule which says :

=F6>0 , applies to =$F$6

while in F7 through F17 , you will see a rule which says :

=F7>0 , applies to =$F$7:$F$17

Note that the choice of F6 to enter the CF formula , and then copy the rule to F7 through F17 was arbitrary ; you can also choose to enter the first CF formula in any other cell in the range , say F11 , and then copy the resulting rule to cells F6 through F10 , and cells F12 through F17. However , if you do choose to do this , remember to use relative referencing , and enter =F11>0 as the rule for cell F11. Using this method will however make it more difficult to understand the CF rules , since the same rule will be copied to F6:F10 , and show =F6>0 there , while in the range F12:F17 , it will show =F12>0 !

------------------------------------------------------------------------------------------------------------------

2. The second way is to select the entire data range over which you want to apply the CF , and then open the CF dialog. In this case , again remember to use relative references , and use the first / top-most cell in the range as the reference point. Thus , in this case , select the range F6:F17 , and enter the formula : =F6>0.

In this case , when you see the CF for any cell in the range , you will see =F6>0 , and the Applies To range will be =$F$6:$F$17.

Narayan
Click to expand...

Hello Narayan,
I will upload the file,
Can you explain what you want your CF to do?

I want my range turn to green if the value of the cell in the range is bigger than 4.

I am going to upload the file. This time the CF didn't work at all, Please look into uploaded file. I was not even been able to build what have built in previous post.

When I do conditional formatting and use this option:
"Use formulas to determine which cells to format, then CF starts troubling me.
When I do conditional formatting and use this option:
"Format cells that only contain"
This option works easier.


-1. Put in the CF formula for one cell , which is the first / top-most cell in a range , using only relative references ; thus , if your range is F6:F17 , put in the CF for F6 , using F6 and not $F$6. The Applies To range always uses absolute referencing , so that does not matter. For example , if you want that a particular format should be applied to F6 if it is positive and non-zero , and another if it is negative , you can use the rules =F6>0 and =F6<0 , not $F$6>0 or $F$6<0.

It doesn't matter to put absolute references, because the excel automatically inserts dollar sign.
This time, when I put function, CF doesn't work at all, it inserts double quotation on the format values where this formula is true.

After resolving that CF is working, then I can use one of the approaches for pasting the CF to other cells.

Narayn,
Let us resolve this here and then continue with the rest. Why CF is not working at all?
Thanks,
GN0001
 

Attachments

  • Conditional Formatting discussion2.xlsx
    10.1 KB · Views: 4
Hi ,

You have used the following 2 rules :

=">=4"

=">=$E$8"

In both of these , the reference point is not mentioned ; suppose you select the range E4:E19 , and you wish to specify a CF rule , the cell reference E4 has to appear somewhere , so that all the cells in the selected range will be formatted based on whether they fulfill the specified condition ; thus if you specify the rule as :

=E4>=$E$8

then every cell in the selected range will be checked against E8 ( this remains fixed because of the $ sign used before the row number.

As far as the first rule is concerned , it should be changed to :

=E4 >= 4

Applied to the range E4:E19 , Excel will check each cell to see if it is greater than or equal to the constant 4.

Narayan
 
Debraj said:
Hi Guity!

But your CF is working perfect..

View attachment 6553

  • In your first CF.. you have asked excel to
If F7(one cell below.. from the starting location F6..) is greater than 4, then, apply RED Format to the F6..​
and just above the 1st 4, 3 is in RED.

  • In your 2nd CF, you have asked excel to
If F*(two cell below.. from the starting location F6..) is greater than 4, then, apply BLUE Format to the F6..​
and just 2 step above the 1st 4, 2 is in BLUE..

If you surprise.. then.. try to update CF Formula as..

View attachment 6554
Click to expand...
Thank you for your time to respond to this post. Thank you for giving this update to me. It all worked.
GN0001
 
NARAYANK991 said:
Hi ,

You have used the following 2 rules :

=">=4"

=">=$E$8"

In both of these , the reference point is not mentioned ; suppose you select the range E4:E19 , and you wish to specify a CF rule , the cell reference E4 has to appear somewhere , so that all the cells in the selected range will be formatted based on whether they fulfill the specified condition ; thus if you specify the rule as :

=E4>=$E$8

then every cell in the selected range will be checked against E8 ( this remains fixed because of the $ sign used before the row number.

As far as the first rule is concerned , it should be changed to :

=E4 >= 4

Applied to the range E4:E19 , Excel will check each cell to see if it is greater than or equal to the constant 4.
Narayan
Click to expand...

Hello NARYANK991,
After spending a lot of time on it, I think, I might get hang of it. I think we should set our formula to a constant rather than to a cell reference. Set the function to a cell reference doesn't bring back the correct result all the time. (this is what I think). Regardless of what approach we take, copy, paste special or formatting brush or selecting the whole range in the "Applied to Range" . The second concern is if we set our function as
=E4 >= 4 , if we are in cell E5, CF compares the value of E4 with the constant 4 and gives the result of that in E5 Or it really compares the value of E5 with the constant 4)?

Regards,
GN0001
 
Somendra Misra said:
Hi GN0001,

Can you check the uploaded file.

Regards,
Click to expand...
Somendra,
Your conditional formatting is not correct.

Because your rule says =E4>= $E$8
And when you look into cell E5, we can see that the cell E5 is formatted, which is not correct. This is what I think,
Regards,
GN0001
 

Attachments

  • upload_2014-5-19_13-3-15.png
    upload_2014-5-19_13-3-15.png
    273.5 KB · Views: 6
The range of the CF is =$E$4:$E$19. both CFs are working, alter the ranges and you will see this.
 
Last edited:
Ok
bobhc said:
The range of the CF is =$E$4:$E$19
Click to expand...
Hello bobhc,
Then E4 is not >= $E$8, why cell E5 is high lighted? I said I think and I never every say never. From what I have understood from conditional formatting, this can't be correct, however I might be wrong.
Thanks,
GN0001
 
bobhc,
Then I don't understand the meaning and functionality of CF.
In E5:
E5 is not bigger or Equal to E8? why should it be high lighted? Please remember I can't understand it, I am not just arguing with anybody?
Regards,
GN0001
 
I take it you are referring to the file somendra has uploaded, I have looked and E5 is not highlighted, I think you are getting confused I think you are referring to cell E8
 
bobhc,
If you are referring to this file Uploaed_2014-5-19_13-3-5, the cell E5 is highlighted.
Regardless if it is highlighted or not, if we have a range of incremental numbers from 1-16 in a range of E1 to E16 and if we say highlight where E4>=$E$8, while E4 is equal to 4 and E8 is equal to 8, then if we have E5 is highlighted, then the cf has not functioned correctly.
This is what I think,
GN0001
 
Then the file must change as it downloads because E5 is not highlighted it is in the range $E$4:$E$19 but the CF starts at E8, if you altered the range to =E4>=$E$5 then all cell would be highlighted, do as I suggested in a previous reply and try altering the ranges yourself to see what happens.
 
Hello bobhc,
Then, if it is the case, it is correct. I must have changed during working with it. It seems to me that even the screenshot shows cell E5, but anyway, I don't have time to look at the file one more time. He is an excel Ninja, he is probably right.
Thanks,
GN0001
 
Hi GN0001,

Lot of confusion, the file I uploaded has one rule for the range E4:E19, which states that =E4>=$E$8. So anytime any cell value in the this range is more than or equal to E8 value i.e. 5 will be highlighted.

Where you found it wrong?

Regards,
 
Somendra,
I have uploaded these files in the beginning of this thread, do you think CF in them are correct?
Please advise me.
Thx,
GN0001
 
@GN0001

CF rules are working for the values they are set for.

Say for example, you put the rule =E4>=$E$8 in the range E4:E19, so in every cell this rule is checked against cell value with E8 cell as E8 is absolute ref. in the formula.

So when the rule is in say E10, the rule will check E10 value is grater than or equal to E8 value and than apply CF accordingly.

Can you please tell where you are getting confused.

Regards,
 
You must log in or register to reply here.
Back
Top