subrata.tatamotor
I have a spreadsheet with two different value for example
column a column b column c
100 100
200 150
80 100
i want if column a data match with the col b data automatically colour formatting done with diff value if not match diff colour appearing on the col c

From India, Pune
prodip_bata
15

Subrata, you can try the following to format the column on conditions.

Based on your query, I think you are trying to colour the format of column C(from cell C1:C3) on satisfying following conditions:

1) If cell value of Column A matches with cell value of Column B ( here I have marked the cell in Yellow colour in respective cell in column C)

2) If cell value in Column A does not match with column B ( here I have marked the cell in Red colour in respective cell in column C)

3) More over I have considered here also,that if there is no value in cell A and B column i mean blank cell then the difference will be appear as blank in column C and the respective cell will be remain with no color.

So to do the conditional format as mentioned above please go through below steps:

Go to Home Menu--> Select conditional format-->Select "Use a formula to determine which cells to format"

Put the format as per following:

=AND(\$A1=\$B1,NOT(ISBLANK(\$A1)),NOT(ISBLANK(\$B1)))

Click "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok". In applies to area, put the formula as =\$D\$1:\$D\$3

Press New Rule. In formula are put the formula as:=\$A1<>\$B1. Select Format and repeat the above step. In here I have selected "red" color. Press "OK" . In applies to area, put the formula as =\$D\$1:\$D\$3 and click on "Apply" button.

=\$D\$1:\$D\$3

For calculating difference of Column A & B put the formula in Cell C1 as follwing:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

The above formula will calculate difference only if there is no cell blank, other wise cell values in Column C will remain blank and no format will be done.

To understand the above please find attached herewith the excel file. For any query feel free to write me: or visit my blog site: Advanced Excel Tips - Manage your MIS Report

From India, Kolkata
prodip_bata
15

Subrata, you can try the following to format the column on conditions.

Based on your query, I think you are trying to colour the format of column C(from cell C1:C3) on satisfying following conditions:

1) If cell value of Column A matches with cell value of Column B ( here I have marked the cell in Yellow colour in respective cell in column C)

2) If cell value in Column A does not match with column B ( here I have marked the cell in Red colour in respective cell in column C)

3) More over I have considered here also,that if there is no value in cell A and B column i mean blank cell then the difference will be appear as blank in column C and the respective cell will be remain with no color.

So to do the conditional format as mentioned above please go through below steps:

Go to Home Menu--> Select conditional format-->Select "Use a formula to determine which cells to format"

Put the format as per following:

=AND(\$A1=\$B1,NOT(ISBLANK(\$A1)),NOT(ISBLANK(\$B1)))

Click "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok". In applies to area, put the formula as =\$D\$1:\$D\$3

Press New Rule. In formula are put the formula as:=\$A1<>\$B1. Select Format and repeat the above step. In here I have selected "red" color. Press "OK" . In applies to area, put the formula as =\$D\$1:\$D\$3 and click on "Apply" button.

=\$D\$1:\$D\$3

For calculating difference of Column A & B put the formula in Cell C1 as follwing:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

The above formula will calculate difference only if there is no cell blank, other wise cell values in Column C will remain blank and no format will be done.

To understand the above please find attached herewith the excel file. For any query feel free to write me: or visit my blog site: Advanced Excel Tips - Manage your MIS Report

From India, Kolkata

 conditional_format.xls (18.0 KB, 451 views)

vibha-saini1
2

Hi
I am using conditional formatting for my attendance sheets, i need a single format style to apply for various leaves and absent.
so i am choosing
format only cell that contain->
cell value -> equal to-> SL
for higlight sick leave
but i need to do same steps for all values. there is no copy option for same formatting.
please suggest if i can do it somthing like
cell value -> equal to-> ="SL","CL",EL","A"

From India, New Delhi
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.