+ Reply to Thread
Results 1 to 15 of 15

Change cell value based on cell color?

  1. #1
    Registered User
    Join Date
    03-28-2008
    Posts
    5

    Change cell value based on cell color?

    Hello,

    I've come across a unique (to me) problem, we had a consultant in and they created a spread sheet. We now want to sort on some values, and ....lo and behold...the consultant color coded some cells but placed no values inside of them....and those are the cells we want to sort on. We have other cells that are totally blank by design on the spreadsheet, so I can't do a bulk replace of all empty cells, I need to somehow read the color of a cell and change the value.

    I'd rather not do it manually, anyone have any suggestions?

  2. #2
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    It would help if we could see the file. Or a small sample of it.

    If we know the criteria that is triguering the conditional formating, it might be possible to do what you want.

    (Zip if possible attach to the thread)
    Last edited by Portuga; 03-28-2008 at 03:30 PM.
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  3. #3
    Registered User
    Join Date
    12-21-2007
    Posts
    65
    Are you using Excel 2007?

    Sorting by color is a custom option in the custom sort in this version.

    you can specify the order that you want the colors to appear.

  4. #4
    Registered User
    Join Date
    03-28-2008
    Posts
    5
    File uploaded. I'm using excel 2003.

    The colors are simply fill colors in the cells, there was no conditional formatting used. I want to read the colors in the cells and replace them with values so they can be sorted upon. The actual value does not matter, as long as I can put values only in the colored cells.....also, in the example, the cells with no color need to remain empty.

    Thanks in advance.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-28-2008
    Posts
    5
    Anyone have any ideas on this one after a great weekend?

  6. #6
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    The cells do have a value - they are blank. Use that as your sort criteria.
    Ed
    _____________________________
    Always learning, but never enough!
    _____________________________

  7. #7
    Registered User
    Join Date
    03-28-2008
    Posts
    5
    Thanks for the help, sorry for the confusion...

    I understand I can sort the blank cells, but I have some cells with color that are blank and some with no color that are not blank. I realized that was not clear in the small example I sent, so I attached another example, a small snippet of the actual sheet.

    I guess it's my fault for not being involved with our consultant....any help is appreciated. It's just under 1000 lines total, so I don't want to do it manually, but I will if needed....
    Attached Files Attached Files
    Last edited by Excel_Drone_VII; 03-31-2008 at 09:22 AM.

  8. #8
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    If you use sort or auto filter you could at least get all the blank cells together so that putting a value in would be simplified.

    Alternatively, someone with some VBA knowledge could write a macro to cycle through the sheet and put a predermined value in the coloured cells.

  9. #9
    Registered User
    Join Date
    03-28-2008
    Posts
    5
    I figured it out, had to hack a quick and dirty VB script together....makes me wish we had excel 2007.....

    Anyway, if anyone needs/wants the answer...to place values into empty cells based on color you first should grab a nifty set of color function extenders. I uploaded them here, you can c/p them into your sheet or just import them.

    I tested the various modules of the script and found that ColorIndexOfOneCell worked for what I wanted, I found the value of the used color (48) and then I put this together.....variable names and such don't make sense, it's not commented, but it saved me a lot of typing.

    Thanks to everyone who replied.

    ==========================================================
    Sub Bulk_Replace()

    'Macro to read color from cell and replace
    'with a value.


    myRows = Application.InputBox("Enter the total number of rows of data")
    myHeadings = Application.InputBox("Enter the number of Heading Rows")
    myColumns = Application.InputBox("Enter the total number of columns of data")
    myColumnsPad = Application.InputBox("Enter the column number to start with")


    For Row = myHeadings To myRows
    For Column = myColumnsPad To myColumns
    output = ColorIndexOfOneCell(ActiveSheet.Cells(Row, Column), False, 1)

    If output = 48 Then
    ActiveSheet.Cells(Row, Column) = "."
    End If

    Next Column
    Next Row

    finished = "Finished processing sheet!"
    MsgBox finished

    End Sub
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-01-2012
    Location
    SoCal, USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Change cell value based on cell color?

    Hello,

    Not sure how the members here feel about me resurrecting a super old thread on my first post, but I have searched high and low for what I am looking to do and this thread is precisely what I am looking for.

    I want to "Change a cells value based on cell color".

    And, if possible, "Change a range or group of cell values based on cell colors (that vary)"

    The only reason I am looking to do this is because I cannot find out how to "shuffle" this group of cells. Originally I wanted to assign these cells numbers and shuffle the whole box (range) randomly.

    So to get around that, I created a 10x10 table of cells with grouped fill colors, then assigned all cells random values with the =RAND() function, then sorted the box based on the random values, copy/pasted a transposed box, resorted, transposed, etc, until I had a completely random box of colors. Now I would like to assign each color it's own value, while keeping the colors exactly where they are. So for example, there are 4 green boxes in my diagram. I'd like to assign all 4 green boxes with a value of 20. There are 8 black boxes in my diagram. I'd like to assign each black box with a value of 10.

    See attached for my diagram.


    Thanks very much!
    Attached Files Attached Files
    Last edited by MrMacroMan; 05-01-2012 at 01:48 PM.

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change cell value based on cell color?

    MrMacroMan,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  12. #12
    Registered User
    Join Date
    05-25-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Change cell value based on cell color?

    Hi,

    Would you be able to upload a sample file with that VB script please. I need a help with similar situation that you explained here...

    Thanks
    shan

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change cell value based on cell color?

    Shan,

    This thread is 4 yrs old and you are not likely to get any response. Its better if you open a thread of your own.

  14. #14
    Registered User
    Join Date
    05-01-2012
    Location
    SoCal, USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Change cell value based on cell color?

    Well... not sure what or which poster you are referring to Shan, but I did find a solution to the issue I had posted about here, and I think it's relevant to the thread.

    See attached for working sheet that solves the issue I had explained. Perhaps this could help.

    The cells in the outlined box can be coded to a value by changing the color to one of the colors shown in C7:C20.

    And the values can be changed to whatever is entered into D7:D20
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    05-25-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Change cell value based on cell color?

    Quote Originally Posted by MrMacroMan View Post
    Well... not sure what or which poster you are referring to Shan, but I did find a solution to the issue I had posted about here, and I think it's relevant to the thread.

    See attached for working sheet that solves the issue I had explained. Perhaps this could help.

    The cells in the outlined box can be coded to a value by changing the color to one of the colors shown in C7:C20.

    And the values can be changed to whatever is entered into D7:D20
    Thank you MrMacronMan, I think i can alter that file... thanks a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1