Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying Values ... (97/SR1)

    Hi,

    I am trying to copy values from a DATA worksheet to a SUMMARY worksheet, under a certain formatting condition. If the values are colored red (or any other colour for instance), then these values are going to appear automatically in the SAMMARY worksheet. Also, I might enter different values the next day and choose to pick a different values as the colour red. How would I set this condition as an equation in Excel without writing a macro? An example spreadsheet is attached.

    Thanks, Hanan.
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Values ... (97/SR1)

    Hi again,

    I tried using VLOOKUP() [if(C4="","",VLOOKUP(yes,$A$3:$C$7,1,False))] by adding another column between B and C and assigning cell C4 the value "yes", but this still does not solves my original question. Any suggestions?

    Cheers.

  3. #3
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Values ... (97/SR1)

    Refer to the posting, "IF statment based on color of Cell (Excel 97/2000)". There are several suggested solutions that would probably work for you, too.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Values ... (97/SR1)

    The posting "IF statment based on color of Cell..." does give me some ideas of how to start. I was thinking that I could you the following formula: =IF(GetFillColor(A2)=6,A2,""), and put it in the SUMMARY sheet. However, I would like the data in the SUMMARY sheet to appear with no gaps between the data. For that I think the OFFSET function will be useful. Would anyone guide me on how I can use this function in this case, or am I going for this all wrong?

    Cheers.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copying Values ... (97/SR1)

    In this case you are using font color, not fill color, so you would use:

    Public Function GetFontColor(rngCell As Range) As Integer
    Application.Volatile
    GetFontColor = rngCell.Font.ColorIndex
    End Function

    HOWEVER, your requirement to have two different groups (red & green) on the SUMMARY WS complicates things a bit, as you can't use some filtering tricks that might otherwise make it easy (such as Data, Filter, Autofilter, Custom, not equal to zero). I can't see an easy use of OFFSET for this requirement (maybe finer minds can). It's easier to write a macro to transpose the DATA to SUMMARY, this can be done using a WS Change event to completely automate it or have it as process to run when requested.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copying Values ... (97/SR1)

    Here's about the best I can do, maybe one of the VBA gurus can clean up after me:

    Option Explicit

    Sub CopyRedGreenFont2Summary()
    Call CopySpecDatatoSummary( _
    Worksheets("Data").Columns("A:A").SpecialCells(xlC ellTypeConstants, xlNumbers), 3)
    Call CopySpecDatatoSummary( _
    Worksheets("Data").Columns("C:C").SpecialCells(xlC ellTypeConstants, xlNumbers), 4)
    End Sub

    Private Sub CopySpecDatatoSummary(rngSourceRange As Range, _
    intFontCol As Integer)
    Dim rngSourceCell As Range
    Dim intTargRow As Integer
    intTargRow = 4
    For Each rngSourceCell In rngSourceRange.Cells
    If rngSourceCell.Font.ColorIndex = intFontCol Then
    Range(rngSourceCell, rngSourceCell.Offset(0, 1)).Copy _
    Destination:=Worksheets("Summary").Cells(intTargRo w, rngSourceRange.Column)
    intTargRow = intTargRow + 1
    End If
    Next rngSourceCell
    End Sub

    HTH.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •