• 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.

How to use CF in multiple cells

V

VDS

Member
@Dear All,

I attach herewith sample file contains TDS deducted, receipts, and balance.
First all deductions are entered and then receipts. Balance is taken by formula

Here, the following are the queries :

a) when the balance is Zero (deducted and receipt is same), all three columns should be coloured with green.
b) When the balance is more than Zero, only TDS received column will be coloured as Red
c) When the balance is less than Zero, only TDS received column will be coloured as Yellow

There are nearly 60-70 rows. How to apply this in all the rows.


VDS
 

Attachments

Select B3:D6
Apply 3 CF's
Using a Formula

=$D3=0
Apply Green Color

=$D3>0
Apply Red Color

=$D3<0
Apply Yellow Color

See attached

In your example select the 60-70 rows to apply it to first
 

Attachments

Hi Santhosh ,

Hui's suggestion will do for your first rule ; if you want only one column to be colored for the remaining two situations , then you will need to use a formula which takes the column also into account , as in :

=AND($D3>0,COLUMN()=3)

If the above formula is entered after selecting the entire data range , then when ever the cell in column D is greater than 0 , only the corresponding cell in column C ( TDS Received ) will be coloured.

A similar rule will have to be inserted for the YELLOW color , as follows :

=AND($D3<0,COLUMN()=3)

Narayan
 
@Narayan Sir,

This is good. Thank you so much.

Small doubts,

a) Excel 2003 provides maximum 3 rules for CF. If the criteria is more than 3, how to apply ?
b) I dont want to use the colouring in print out. Is it possible ?


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