Forum Discussion
Help with excel formula relating to certain coloured text
I have a spreadsheet that I use for bills of how much and when they are due to be paid, I'm trying to find out if there is a formula that enables me to pick out a certain coloured text e.g. column A that has the amount due to come out and I have used a certain colour text to correspond with the date of the month it is due out, I then want to add the data into different columns for each week of the month. Is this possible and if so how?
7 Replies
Excel formulas can’t read text color. To organize by week, it’s best to use a helper column (e.g., “Week 1”, “Week 2”) and then use SUMIF or FILTER.
If you must use text color, you’ll need VBA. Example:
Function SumByFontColor(rng As Range, colorCell As Range) As Double For Each cell In rng If cell.Font.Color = colorCell.Font.Color Then SumByFontColor = SumByFontColor + cell.Value Next End Function
Then use in Excel like:
=SumByFontColor(A1:A100, C1)
(C1 has the font color you want to match.)See the attached workbook.
Thank you, unfortunately I'm not particularly computer savvy but will see if I can have a go.
The most a function can do at present to detect color is very limited.
If the cell contains a negative value that is custom cell formatted to appear in red, for example, you may use CELL to determine if it contains color:
E1 is custom cell formatted with: [Red][<0]0;[>0]0
Thank you
spreadsheet formulas do not detect text color (nor most other formatting). you CAN use conditional formatting to format values based on built in or custom rules to apply a particular text color (or other formats like cell/background color). And cell formulas will not move data but you can have a formulas that will 'add'/show data in a table/range. A sample sheet and more specifics of what you are looking for would be helpful to make more specific suggestions. But basically you can have a custom rule in conditional formatting to set the text color and then in those different columns for each week of the month you can have lookup formulas (e.g. XLOOKUP, FILTER) to pull and show the corresponding data.
Thank you I will have a go
