Check for a particular value and formatting the cell in another sheet

[sindhuja]

Hi All,

Here is my requirement.

I have a sheet named "source". I need to look for the text "Net" then the amount and the fnum.

Look for the amount and Fnum in the sheet "sam1". if the same amount and the fnum is found then the amount should be bolded, italized then the font size should be increased to 14.

Similarly we should look for the all the values with the text "net". variance of .01 need not be considered. for ex in the source shet if the value is 350.00 and in the sam1 sheet if the value is 350.01 for the fnum 1163 then also formatting to be done. Whereas if it is 351.00 in sheet sam1 then it should be highlighted with some color.

-Sindhuja

[mdmackillop]

Can you post the code you have created to do this? That way we can assist your undertstanding, rather than just providing a solution.
Regards
MD

[sindhuja]

Hi Md,

I tried to work out and i am able to find only the value of amount to be compared and the fund.

Have attached the coding and am not sure whether am in right track...

amount and fund found to be checked in the sheet sam1 and if same then the font size to be increased, bolded and the italics format.

Helpme out in proceeding further.


Option Explicit

Sub check()
Dim rFund As Range,
Dim Fund As Long
Dim netamt As Range
Dim FirstAddress As String
Dim i As Long

With Sheets("source").Columns(3)
Set netamt = .Find(What:="Net", _
LookIn:=xlValues, lookat:=xlPart, after:=Range("C1"), searchdirection:=xlNext)
FirstAddress = netamt.Address

Do
If Not netamt Is Nothing And netamt.Offset(3, 3) <= 0 Then
'here we should check if the value of net is equal to +.01 or -0.01
'we should leave that and search for another.

Do
Set netamt = .FindNext(netamt)

Loop Until Not netamt Is Nothing And netamt.Address <> FirstAddress
End If

Set rFund = .Find(What:="FUND #:", LookIn:=xlValues, _
lookat:=xlPart, after:=netamt, searchdirection:=xlPrevious)
Fund = netamt.Offset(-1, -1)

Set netamt = .Find(What:="Net", _
LookIn:=xlValues, lookat:=xlPart, after:=netamt, searchdirection:=xlNext)

Loop Until Not netamt Is Nothing And netamt.Address <> FirstAddress
End With

End Sub


-Sindhuja

[sindhuja]

Any help on this plz....

-Sindhuja

[sindhuja]

Anyone got a chance to look into this thread....

[mdmackillop]

Option Explicit
Sub check()
Dim c As Range
Dim FirstAddress As String
Dim i As Long
Dim k As Long
Dim arr
'Set array sioze to suit data
ReDim arr(2, 100)
'Look for Net
With Sheets("source").Columns(3)
Set c = .Find(What:="Net")
FirstAddress = c.Address
Do
If Not c Is Nothing Then
i = i + 1
'Write FNUM and Amount to array
arr(1, i) = c.Offset(-1, -1)
arr(2, i) = c.Offset(2)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End With
'Resize the array to items found
ReDim Preserve arr(2, i)
'Look for array values in Sam1
With Sheets("Sam1")
For k = 1 To i
Set c = .Columns(4).Find(arr(1, k), lookat:=xlWhole)
If Not c Is Nothing Then
'Check difference in values; carry out formatting
If Abs(c.Offset(, 3) - arr(2, k)) <= 0.1 Then
With c.Offset(, 3).Font
.Size = 14
.Bold = True
End With
Else
c.Offset(, 3).Interior.ColorIndex = 35
End If
End If
Next
End With
End Sub

[sindhuja]

Thanks a lot MD.....:friends: