Wondering if it could be done

[Spare_Key]

Good day everyone,

I am a rookie soft and true, been lurking here for a about a week or two. I require some excel assistance with an excel I am working with. I have a great idea but I dont know how to apply it as I fairly fresh to VBcode. I am trying to run a formula where a specified cell containg "s" changes the color of the cell (conditional formating) however I would like the "s" removed from the cell. I would like to keep the cell clean so I can sum the numbers up once the data is entered for the month (Each individual colour to have its own sum). I found a Macro online that removed the letters from the cells but removed the conditional formating since the letter was no longer present.

I belive I am looking for three different macros, or one that would work with all of these rules;
-Cell containing specific letter colors cell, letter does not show up in cell.
-Colored cells do not add up with regular cells.
-Coloured cells sum up by colour.


I appriciate any help anyone could give me. If you require anymore information please ask I will check on a daily basis.


Thank you.

 

[problemss]

I am not sure how your data is formatted. I am guessing it is a table, let me know if you think this logic will work for you.

Create a new column called 'group'. write a macro (or formula depending if this is a one time or many time operation) that will set a unique group code (1-4 for 4 different colors) depending on your criteria. So if the cell has an 's' it will been group 1, if it had a 'd' group 2, a number group 3, and none of those group 4. Based on that group number you can color the cell and you can easily create a sumif to sum them.

Work this logic work in your sheet? Will this be a one time operation or will you need to reuse it for changing data?

If this works, can you post a snapshot of the data and the logic you need?

 

[Spare_Key]

Thank you for your prompt reply.


This is a monthly sheet with numbers that refrence to yearly summary sheet. I would need all of these numbers to add up to their totals and then refrence back to the summary sheet. This sheet will be used for years to come.

 

[problemss]

So...... Are you able to create the helper column? Is the logic right for your sheet? Can you post a snapshot of the data?

 

[Spare_Key]

I have googled the helper column but i dont really understand the concept.
The data will be filled in as the screen shot i have provided in my previous post.

 

[problemss]

I don't see anything attached, it is probably being blocked by our firewall. I will check on the image later, or you can do a copy and paste of the table directly into your reply.

 

[problemss]

Just a thought, since you are able to scan through, find the cell and remove the letter. Use this to change the color of the cell while you are removing the letter.

You can use ColorIndex (to use one of the 56 "preset" colors in Excel):
ActiveCell.Interior.ColorIndex = 36


Or you can use Color:
Range("A1:A6").Interior.Color = RGB(200,160,35)

*Change cell background color using VBA - Excel

 

[problemss]

Just saw your pic. You are on the right track. You can loop through your cells and remove the letters (s,p,a,f,...), when you remove the letters you can apply your color to the cell using the link I just posted. To sum the contents I would use the UDF mentioned in the link below.

Sum Cells based on Background Color

 

[Spare_Key]

problemss - Thank you very much for your help with these issues. The last link you posted i cant access as our firewall is blocking the access to the site.

You can loop through your cells and remove the letters (s,p,a,f,...), when you remove the letters you can apply your color to the cell using the link I just posted

The main thing that i didnt add in my original post is that this excel will be shared with multiple users with limited access and they are going to only populate the cells with the numbers. I would want the sheet to do all of the work instead of me going back and forth through the sheet with all of the multiple users and data. I am the over seer of the excel and i am just trying to improve it. I believe my idea to simplefy some steps are great however the bullet im biting is quite complex.

Getting the users to color the cells as per their specific needs or criteria might just work. My question to that is, if you have red cells and blue cells and then the regular cells with data the sum function will collet all the data from every cell in the specified row. How would I split up that function so that the reds dont mix with the blues that dont mix with the whites?

 

[problemss]

Will your users also put in the letter?