Posts by wildgr81

Greetings!

I was copying cells from one spreadsheet to another using the following IF statement. It basically says that if the cell in this spreadsheet is blank, then put nothing in there. If it is not blank, put in data from another spreadsheet. The problem is that when it copied the information over, it was in a completely different color than the original cell. So, for example, if I put the following formula in the new spreadsheet, it will copy the cell information from the listed spreadsheet in this formula and place it in the appropriate cell, but it is changing the data from its original color to green. Any suggestions?

=IF($I2="","",'[Reinstatement Template--KGM.xls]Car Care Non CA'!L1)

Kevin

Re: Extract Text In Single Cell Based On Font Color

This information can be useful if I knew a lot about VBA. However, I don't. So, when you are posting answers, please give specific help or what areas of the formula I would need to change so that I know what to enter into VBA. I know Excel formulas. I just haven't worked with VBA that much. Thanks!

Kevin

Re: Extract Text In Single Cell Based On Font Color

Hi!

Thanks for the quick response. I am attaching an example to this response. As for the colours needing to be different, yes they need to be different because that is how I am receiving the data from another user. If it is impossible to extract the information by colour, I might have to contact the other user and see if there is a way for them to break out the formula into different cells. However, at this time, it isn't an option. I receive the formula in the Excel file and some of the formula is coloured red and some of it is coloured blue and I need to find a way to disregard the blue information and just add up the red information. As I said, I created an IF statement to pull the informaton I want from the formula. For example, in the formula PREM + PRDCMP + ACQ + VIP - CANCEL FEE, I have an IF statement that says if the column heading is in the above formula, take the amount located directly under that column heading and put it in that column, in the blank space next to the formula. If the column heading isn't in the formula, the IF statement will enter 0 in that column, in the blank space next to the formula. Then, the last blank cell of that formula row sums up the amounts in the formula row. All I need now is to have Excel take and look at the colours in the formula cell and if any part of the formula is in blue, it should put a zero underneath the appropriate column heading, in the blank cell next to the formula. I hope this and the attachment helps to explain it a bit better.

Thanks again!

Kevin[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi,

I'm sorry...the example that I attached wasn't as good as I thought it was because it didn't show multiple colours in a cell. A better example is attached. Thanks!

Kevin

Greetings!

I have a very unique problem. I have a cell that has a formula in it like PREM + PRDCMP + ACQ + DLR in a spreadsheet that looks like this:

PREM PRDCMP ACQ DLR VIP COMM TOTAL
2 1 3 4 6 5

PREM + PRDCMP + ACQ + DLR 2 1 3 4 10

where PREM + PRDCMP + ACQ are colored red and + DLR is colored blue. This formula is entered in that particular cell as a text string. I have a formula in the PREM column that says if the word PREM is located in the formula to the left (and it is), then put the value that is located right under the PREM column (2) in the blank cell next to the formula. So, in the above example, I have an IF statement that sees that PREM is in the formula to the left and so underneath the PREM column, next to the formula, it would place the number 2. Now, I need Excel to detect if any word in the formula text string is colored blue and if it is, to put a zero in that column. So, in the formula above, if + DLR is the only text that is blue, I want to have Excel add 2 + 1 + 3 only. I want to have Excel take any string out that might be colored blue, such as + DLR and return the value of zero for it. The purpose of the different colors in the formula is to distinguish which information is relevant and which information should be ignored. So, I'd like Excel to read the above formula and say put the PREM amount in the cell under PREM, put the PRDCMP amount in the cell underneath PRDCMP, put the ACQ amount in a cell under ACQ and put zero in the DLR column since it is colored blue. If any of them (PREM, PRDCMP, ACQ, DLR, VIP or COMM) are colored blue, I want Excel to put a zero in that column. Is this possible? I apologize for being redundant, but I wish to be thorough. Let me know.

Thanks!

Kevin