Formula That Returns Result & Font Color Of Referenced Cell
Apr 3, 2008
I have 2 tables, 1 beneath the other - table 1 columns represent date ranges and their values. Columns and their data are alternating color coding. Table 2 references, by means of array formula, this data - IS it possible to include the font color as a result?
I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.
For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.
What I wrote as my formula is as follows-
=IF(A1=3,"TEST")+IF(A1=4,"RESULT")
It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.
I have a simple formula subtracting one cell from another using =SUM(XX,-XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either.
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'. e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I have a spreadsheet that contains the 5 digit numbers in the rows and the columns respecstively. I'd like a formular or macro to change font color for each cell. If the combined value of the 5 rows are greater or less than the combined range 87030 and 87200, the 5 cells will be changed to Red. If:.........
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
VB:
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String StringReturn = Str1 & Str2 & Str3 End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
I am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
I have a two different formulas the return a numbered result(PO Number) in the same column. I then vlookup both of them with the same formula into a pivot table, one returns the result one doesn't. format appears to be the same.
I have created a table in excel, and it has a very simple sum totals. When i include all cells in the table the total becomes 0. When I exclude a cell in the table, the total shows. I have deleted the row and re inserted another one and copied the cell content from above that was not having a problem,
I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.
It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
I have font color white in blank cells in column E and I (from row 5 to row 245) so the visitors will not see the text. If any of these cells become yellow (color code is 6), the font color will become black so visitors can see the text alot far better than white. I've tried this code myself after this post but nothing happen
I need a formula to count cells based on the date, so that I can have a blank cell when the answer is 0. I am adding values cumulative and future cells need to be blank because I have a graph that has a trend line and I don't want the trend line to fall off at the end. I also don't want to have to go back to this every month and update it.
I am trying to figure out what the formula would be to change the font color if the entered number is less than the quota.
I'm making a spreadsheet for work that each of the reps will punch in their daily numbers, and, for example, if there quota was 70 and they are <70 than the font will turn red, and if greater or equal to 70 it will turn green.
Is there anyway to change just the color of the first result? The "2" to red?
I cannot do it in the cell because of the 2 results being combined into 1 cell so I'm wondering if the formula can be modified to change the color of the 1st result.
This appears to be so simple, I've searched the forums and have found almost the right VB code but not quite. I have a 3000 row spreadsheet with various columns. Column M can either be PASS, FAIL, REJECTED, IN PROGRESS or NOT ASSESSED. I need the rows (A to M inclusive) to change colour and text colour dependent on this condition.
I almost had an answer with Select.Case but I couldn't get the formula to work with text, only numbers.
"I want to make a spread sheet where the "shading" changes based on the text entered into the cell. ie I want to be able to enter yellow into a cell and have the cell shading change to yellow."
I have a table - the first column is quantity, the others are descriptions, prices, etc.
I need to change the font of the other columns to white if the quantity cell is empty.
Jennifer Auto Merged Post;I'm still working on this, making baby steps. This is my code so far:
If Range("B26") = IsBlank Then Range("C26:K26").Select Selection.Font.ColorIndex = 2 End If
It works for line 26. Can someone give me a hint about how to make it do the same things for lines 27 through 62? Without having 35 IF statements in my code?
I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.
i have had to change the quarters that our company works in and in doing so have to make some changes to my VBA code. our quarters have moved back 1 month so Q1 = Dec/Jan Feb now.
below is a copy of the code that used to make the associated cell font color red depending on the current date. i need to change it to be relevant to the new quarter structure.
[Code] .....
As you can see if the month value is less than 4 (jan/feb/march) it will highlight red....however i dont know how to change the range to specifiy the ranges 12,1,2 as per the new quarter structure.
I have a database which I want to send to my peers. I want to set a rule which will enable me to keep track of the data which are changed. For instance, in a cell if someone edit that number, the color will change from black to red.
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
Cell B157 is changed by checkbox 1= TRUE, 0 = FALSE Cell H1 and Cell H2 are percentages changed by the user.
Range(AI8:AQ8,AI12:AQ12,AI16:AQ16,AI20:AQ20, etc) each cell in the range contains a formula, and is protected from the user.
I need the contents of each cell in the range to change font color (independent of each other) when the formula of any of the cells within the range changes - to the following
If B157 = 1
Cells < H1 (a percentage changed by user), would turn Red Cells > H1 but < H2 (a percentage changed by user) would turn Yellow Cells > H2 would be Green
If B157 = 0
Cells < H1 (a percentage changed by user), would turn Green Cells > H1 but < H2 (a percentage changed by user) would turn Yellow Cells > H2 would be Red
I need it to fire whenever the value of one of the cells in the range changes.
I have tried If statements, Select Case, and a combination of both but just can't get it!
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.
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
