possible to drag the conditional formatting formula start from =$B3$ to =$B$100 automatic

[cboys00]

the cell to insert Display the formula for easy to read
conditional formatting the formula in D3 until D100 is same $B$3
cell value 0 $B$3
1 $B$3
1 $B$3
1 $B$3
0 $B$3



my question:


is it possible to drag the conditional formatting formula start from =$B3$ to =$B$100 automatic
not need to edit one by one from $B$3 until $B$100

for more detail see the attachment file, thanks

[dilipandey]

Hi cboys,

You can simply copy D3 and paste special formats on the below cells until D100 or do you need some other thing ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

See attached file where I have done the same :-

conditonal formatting(1).xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[olasa]

In the conditional formatting, remove the $ sign for row --> $B3
This will leave the conditional formatting formula free to move when copied downwards. Otherwise will be fixed to row 3.
Example: In cell E3 use this conditional formatting formula: =($B3=1). Copied down it will show =($B4=1), =($B5=1), etc
//Ola

[cboys00]

In the conditional formatting, remove the $ sign for row --> $B3
This will leave the conditional formatting formula free to move when copied downwards. Otherwise will be fixed to row 3.
Example: In cell E3 use this conditional formatting formula: =($B3=1). Copied down it will show =($B4=1), =($B5=1), etc
//Ola

if copy and paste.....deadlah...i have a lot cell to paste...more 400 cell

[olasa]

Copy the first cell in the range, and copy to the other 399 cells in the range, in one go - after the $ sign adjustment in the first cell.
//Ola

[cboys00]

Copy the first cell in the range, and copy to the other 399 cells in the range, in one go - after the $ sign adjustment in the first cell.
//Ola

"In one go" ---how to do that.....is mean by drag??

[cboys00]

[QUOTE=dilipandey;2859326]See attached file where I have done the same :-

Attachment 168357


sorry sir, in attachment, didn't see any new idea....?

[dilipandey]

May be...
1) select first cell
2) press ctrl +C
3) press down arrow to select below cell
4) press Ctrl + Shift + down arrow
5) Paste special -> formats
6) done

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

sorry sir, in attachment, didn't see any new idea....?

You were asking to drag the conditional formatting then why would you see any data. I have dragged the conditional formatting from B3 to B100 in one go by following the steps mentioned in post #9. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[cboys00]

sorry wrong mention

I want to drag the rule cell from $B$3 until $B$100 in cell D

example: in cell D3 until D103

$B$3
$B$4
$B$5
$B$6
$B$7
$B$8
$B$9
$B$10
$B$11

i attach the again

[dilipandey]

Hi cboys00,

Do you :-
1) Want to apply the conditional formatting
2) want to change the conditional formatting
3) want to use this in a formula not in conditional formatting

??

Also what are you referring by saying "rule cell" ?

In your attached file I did not find any conditional formatting.. If you want to drag the conditional formatting, just remove the dollar sign ($) from row number (but keep this with column and drag down....if you want to use absolute reference (fixed reference) like you shared, then $ sign does'nt matter because if you drag the formula down (even without $ sign), reference will still be change like B3, B4, B5. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[cboys00]

thanks to mr dilipandey and olasa, had found the solution yesterday night.

just type =(B3:B103) in rule cell.

then in cell D, just drag until D103... yet..

[dilipandey]

Rule cell = (B1:B103) ... I am not sure what purpose this is fulfilling

Regards,
DILIPandey

<click on below 'star' if this helps>

[olasa]

Good.
When your problem is solved, please mark the thread as Solved under the Yellow bar, Top right, under Thread Tools.
//Ola

[cboys00]

ok thank you very much to olasa and DILIPandey