Conditional Formatting - copy paste to adjacent cells

Question

• How do I copy and paste conditional formatting for a series of cells - incrementing the cell positions accordingly?

For eg:

I have 12 columns for each month of the year...

Each column has the sales performance of the staff in that particular month.

If the staff has performed below target for 3 continuous months, that 3rd month should be highlighted in red.

Right now, i have put the below formula in the column for March

A1 = Staff Name

B1 = Target for the year

C1 = Jan

D1 = Feb

E1 = Mar

=and(c1<b1/12,d1<b1/12,e1<b1/12)

if the above formula is true, then it highlights in red..

how do i drag this formula to other adjacent cells such that in the month of July, it will automatically check if May, June and July has less than the target figures.

Answer 1

Make the CF for cell A1 "CellValue not equal to  0" and apply formatting

Make the CF for cell A2 "Formula is =$A$1<>0" and apply formatting

Then, you can select A2 and then the Paint Brush and then select whatever other cells you want the same CF is A1 does not equal zero.

hth

Vaya con Dios,

Chuck, CABGx3

Answer 2

Make the CF for cell A1 "CellValue not equal to  0" and apply formatting

Make the CF for cell A2 "Formula is =$A$1<>0" and apply formatting

Then, you can select A2 and then the Paint Brush and then select whatever other cells you want the same CF is A1 does not equal zero.

hth

Vaya con Dios,

Chuck, CABGx3

 

Hello,

I am facing the same problem. I have a set of data in which column B have different values and I want for one specific value (Text) it should highlight all the adjacent cell from A column till G column. Please help!

Kunal

Hi Kunal.......

First, highlight A1:g1

Then, on the HOME Tab, select Conditional Formatting, and then New Rule.

Then select "Use a formula to determine which cells to format"

Then in the box marked "Format values where this formula is true:, type =$B1="yes"

Then click the Format button and select your format, say maybe Fill Tab, the choose Yellow box, then OK.

Then OK again,

Then click on Row 1 to highlight the entire row, and choose the Home Tab, Paintbrush.

Then click on the rest of the Row numbers you wish to have the sale Conditional Format.

When finished all Column A:G in all rows that have "yes" in column B will be highlighted.

HTH

Vaya con Dios,

Chuck, CABGx3

Answer 3

Make the CF for cell A1 "CellValue not equal to  0" and apply formatting

Make the CF for cell A2 "Formula is =$A$1<>0" and apply formatting

Then, you can select A2 and then the Paint Brush and then select whatever other cells you want the same CF is A1 does not equal zero.

hth

Vaya con Dios,

Chuck, CABGx3

 

Hello,

I am facing the same problem. I have a set of data in which column B have different values and I want for one specific value (Text) it should highlight all the adjacent cell from A column till G column. Please help!

Kunal

Answer 4

this works when this is a formula in a cell.

i've applied this formula in a conditional formatting place.... so how do i copy paste the formula from a conditional format??

Answer 5

Just need to add a few asbolute references:

=AND(c1<$b1/12,d1<$b1/12,e1<$b1/12)

When you copy this 3 cells to the right, it will automatically become:

=AND(f1<$b1/12,g1<$b1/12,h1<$b1/12)