Sub formulainvariablerows()
lr = Sheets("Sheet1").Cells(Rows.Count, "a").End(xlUp).Row
'lc = Sheets("Sheet1").Cells(1, Columns.Count).End(xlUp).Column
'I changed to
lc = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("result").Cells(2, "a").Resize(lr, lc).Formula = _
"=COUNTIF(Sheet1!A1,Sheet2!A1:AG1)0"

'I added
With Sheets("Result").Range(Cells(2, 1), Cells(lr + 1, lc))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlEqual, Formula1:="FALSE"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
The macro does copy the formula into sheet2 from row 2 to the last row on
sheet 1.
If desired, send your wb to my address with a snippet of this and complete
details and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shikha" wrote in message
...
Thanks Don, it worked well with a single column data. Now, building on
this
further, my data is present row wise from A1:D1, A2:D2 and so on. Its
working
well on A1, A2 but does not percolate row wise. How to do that?
Second query: I also want the conditional formatting that I have applied
on
first row to be applied to variable number of rows below it. Any
suggestions?

"Don Guillett" wrote:

Sub formulainvariablerows()
lr = Sheets("sheet1").Cells(Rows.Count, "a").End(xlUp).Row
'MsgBox lr
Sheets("sheet2").Cells(2, "a").Resize(lr).Formula = "=a1*2"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shikha" wrote in message
...
Hi All,
I have 2 worksheets, for which I am comparing data and showing result
in
3rd
worksheet, in same workbook. I am able to do this for fixed number of
rows
by
dragging formula in 'result' sheet to no of rows present in Sheet1 and
Sheet2. However, number of rows in sheet1 and sheet2 is not fixed and
I
want
that formula in 'result' sheet automatically gets copied for no. of
rows
in
sheet1, by a click of a button. Is there any way to do this?

Regards,