Posts by pmyk

    I am preparing a report to present it colorfully. It will be something like this. The first word in the Description cell will be the same for a few rows. Then the following rows will begin with another word and this second word will be the beginning word for the following few rows and it goes on like this. Now I can give different font colors as shown above. I would like to give background color also. One BG Color for the first word and another BG Color for the rest of words in that cell. Likewise in the following rows. In this way, Bernard will be on Yellow Background and Breakfast, Dinner & Lunch of him will be in blue background. Similarly, David will have green color background and his expenses will be on pink colour background.

    Is this possible?

    If not, should I be satisfied with the FONT Colors?

    Kindly reply.

    The following code works.

    1. Private Sub cmdMultipleFontColorInSingleCell_Click()
    2. '
    3. Sheets("Sheet3").Select
    4. Range("a6").Select
    5. ActiveCell.Characters(Start:=1, Length:=6).Font.ColorIndex = 3
    6. ActiveCell.Characters(Start:=8, Length:=5).Font.ColorIndex = 5
    7. End Sub

    The following code works.

    1. TxtClr = 16711680 (RGB COLOR)
    2. BgClr = 16775408
    3. Worksheets(ShNam).Cells(RowNum, ColNum).Font.Color = TxtClr
    4. Worksheets(ShNam).Cells(RowNum, ColNum).Interior.Color = BgClr

    The following code is not workng.

    1. Endr = 5
    2. Worksheets(ShNam).Cells(RowNum, ColNum).Characters(Start:=1, Length:=Endr).Font.Color = TxtClr
    3. Worksheets(ShNam).Cells(RowNum, ColNum).Characters(Start:=1, Length:=Endr).Interior.Color = BgClr

    Is it possible to have 2 BG colors in one cell?

    Re: getting cells from Column C if Column A & B match in both files A & File B

    Since no one has replied I am giving you my suggestion.

    Hope this helps.
    Someone may give a better suggestion for the code to run fast if you have more records.

    Re: transfer of value from workbook to a new workbook

    Hope this helps. I am just a learner in VBA.

    Re: vlookup over multiple files in macro

    I got this in one site and I modified it. Hope it may help.
    Private Sub cmd2Bks_VLOOKUP_Click()
    Dim SrchVal As Range
    Dim SrchRng As Range
    Dim FoundOut As String
    Dim WkBk1 As Workbook
    Dim WkBk2 As Workbook
    Dim WkBk2Name As String

    WkBk2Name = "MembrNames.xls" 'modify it as per your requirement"

    Dim WkBk2NamePath As String
    WkBk2NamePath = FilePath & WkBk2Name

    Set WkBk1 = Workbooks("MembrRemarks.xls")

    If IsOpen(WkBk2Name) = False Then Workbooks.Open (WkBk2NamePath)
    Set WkBk2 = Workbooks(WkBk2Name)

    Set SrchVal = WkBk1.Sheets(3).Cells(3, 2) '' value in row 3 col 2 to find
    Debug.Print "SrchVal = " & SrchVal
    Set SrchRng = WkBk2.Sheets(2).Range("B:C") 'source

    FoundOut = Application.VLookup(SrchVal, SrchRng, 2, False)
    Debug.Print "FoundOut = " & FoundOut

    Set SrchVal = WkBk1.Sheets(3).Cells(18, 2) ' value in row 18 col 2 to find
    Debug.Print "SrchVal = " & SrchVal
    Set SrchRng = WkBk2.Sheets(2).Range("B:C") 'source

    FoundOut = Application.VLookup(SrchVal, SrchRng, 2, False) 'col 2 to retrieve
    Debug.Print "FoundOut = " & FoundOut
    End Sub