Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts

    modifying existing cell

    hello all,

    This is my first time post here, however, I have been lurking here for a while and found many of the solutions posted very helpful.

    This current problem is perhaps beyond my current skill level and I have been racking my brain attempting to find a solution for it.

    so here is the situation:

    I have a excel file that I need to modify. The file is rather huge and I would like to automate the actions to a few steps.

    The final result is to have specific rows highlighted in either red or yellow and add text to existing cells based on the results of other cells.

    example
    if F7 = 0
    then entire row highlighted in yellow and add "closed" to D7 while keeping what is already in the cell.

    I have attached a JPG of the desired end result. (I had to cut away most of it due to confidentiality)

    I have developed formulas to highlight the cells using conditional Formatting they are as follow:
    =AND($F3>0,$G3<=0,$F3>=$E3) Yellow
    =AND($F3=0,$F3<=$E3) Red

    these are applied across the cells that I want to affect.

    The only part that I am missing is to added text to a existing cell based on the same results

    If yellow add "CLOSED" to cell D7 for example
    If red add "CANCELLED" to cell D7 for example

    if the add words could input in bold that would be awesome, but not a major point.

    No additional cells can be added, handed down from the supervisor.

    I had developed a formula using the replace function, but as it modified the cell being used as input it didn't work.

    I was speaking with a co-worker and they said that I might have to create a Macro.

    any help anyone could give me would be greatly appreciated.

    Thanks
    Attached Images Attached Images

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You should always supply your excel version.
    It appears that you no longer need the formula so you should use a macro to supply the text and color based on your criteria for that text. Supply sample or dummy sample file with explanation

  3. The Following User Says Thank You to Supershoe For This Useful Post:

    Mortamier (2014-09-09)

  4. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    sorry about that.

    Version is excel 2000

    dummy copy is included, I have added a unmodified sheet.

    explanation:


    I would like to get a MACRO that does the following:

    highlight the rows, and add CLOSED or CANCELLED to column D based on the data listed in the MAX and ENROLLED columns.

    if ENROLLED is 0 I want the row highlighted in red and CANCELLED added to column D.

    if ENROLLED is equal to or greater than MAX I want the row highlighted in yellow and CLOSED added to column D.



    thanks again
    Attached Files Attached Files

  5. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Try attached to see if if works in xl2000
    Attached Files Attached Files

  6. The Following User Says Thank You to Supershoe For This Useful Post:

    Mortamier (2014-09-10)

  7. #5
    New Lounger
    Join Date
    Sep 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    wow!

    yeah that worked! That worked awesomely!

    How would I go about exporting that to another file?

  8. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts

    Smile

    Best to import your data to this sheet. However, IF you want to use elsewhere you need to make sure that range(i1:j4) remain as they are without ANYthing in the blank cells on row 1 or 3. If not i1 then change the macro. Assign a button.

    The macro or entire macro module may be copied to another file set up EXACTLY as this one.
    I have changed it so that it automatically removes color and extra text before re-doing completely so you can UPDATE.
    Attached Files Attached Files

Posting Permissions

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