PDA

View Full Version : Solved: VBA help required



satish gubbi
02-26-2012, 08:34 AM
Hi

I have the below conditional format which will highlight the column A:J if column J value is <>0

=$J18<>0

I need code to apply this conditional format to the whole sheet, and once highlighted, need to move the highlighted data to other sheet

kindly help

Xrull
02-26-2012, 11:15 AM
Satish Gubbi:

I'd suggest you change the title of your post.

This code copies the row information if you enter a number in a cell in column I.

'Posted by: satish gubbi
'Reply: Xrull
'Link: http://www.vbaexpress.com/forum/showthread.php?t=41109

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wksDec As Worksheet
Set wksDec = Sheets("USD DEC11")

Dim wksDecCpy As Worksheet
Set wksDecCpy = Sheets("USD DEC COPY")

Dim lngRow As Long
lngRow = wksDec.Cells(Rows.Count, 1).End(xlUp).Row

Dim sglIHB As Single

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range("I18:I" & lngRow)) Is Nothing Then


sglIHB = wksDec.Cells(Target.Cells.Row, 8).Value - wksDec.Cells(Target.Cells.Row, 9).Value
wksDec.Cells(Target.Cells.Row, 10).Value = sglIHB

If sglIHB <> 0 Then

wksDec.Cells(Target.Cells.Row, 1).Resize(, 10).Copy Sheets("USD DEC COPY").Cells(Rows.Count, 1).End(xlUp)(2)

End If

End If

End Sub

satish gubbi
02-27-2012, 08:58 AM
Thank you very much Xrull, its working as intended