VBA - Macro

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
how would I add to this macro, so it would color the cells blue after putting them in the desired column?

Selection.Interior.ColorIndex = 33


[
Sub Rectangle221_Click()
Dim i As Long
'find the column# where the date is
With Rows(2)
Set c = .Find(Range("aj116").Value, , , xlWhole)
If Not c Is Nothing Then
rfnd = c.Column
End If
End With

'find the match currencies of all the records from aj117 to last row
'and put the values in desired date column
For i = 117 To Range("af" & Rows.Count).End(xlUp).Row
With Columns("b")
Set fc = .Find(Cells(i, "af").Value, , , xlWhole)

If Not fc Is Nothing Then
On Error GoTo err:
Cells(fc.Row, rfnd).Value = Cells(i, "ag").Value
End If
End With
Next
'if no date found from row 2
err:

End Sub
][/code]
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
Add

Cells(fc.Row, rfnd).Interior.ColorIndex = 34

If you want another color, just record a macro where you format the cell the way you want, stop recording and check out what the macro did.

K
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
Add

Cells(fc.Row, rfnd).Interior.ColorIndex = 34

If you want another color, just record a macro where you format the cell the way you want, stop recording and check out what the macro did.

K

This didnt work, it only colored couple of cells that have data, not all the cell. i need all the cells that have data
 

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
If

Cells(fc.Row, rfnd).Value = Cells(i, "ag").Value

is where you put them in the correct space, then

Cells(fc.Row, rfnd).Interior.ColorIndex = 34

should set them to the correct background color if placed on the next line of code. What I mean is that if your first line works, then there is no reason my second line shouldn't.


K
 

Forum statistics

Threads
1,141,681
Messages
5,707,791
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top