I would like to have cells that conditionally format the font color in the cell based on the background color of the cell. Essentially I have a matrix with some cells highlighted in red, yellow, or orange. There are values in each cell of the matrix. If a value is 0 I want the font to be grey. If the value is NOT 0 AND the cell backgorund color is NOT white, I would like it black and bold. Is there any way to do this in excel or using VB?
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.
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 a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
I have 2 different worksheets...each of them containing item numbers. On Sheet1, Column A contains the item numbers. I have conditional formatting to change the interior color of column A based on several criteria.
On Sheet2, I also have item numbers, but they are located in Column B. If item 12345 in Column A on Sheet1 has an interior color of yellow, I would like that same item on Sheet2 to have an interior color of yellow.
I'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)
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 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
In excel 2000, I am trying to count the lines in a spreadsheet that has the first cell in each row highlighed in yellow. Additionally, I am trying to count the number of rows that has a red font used in the cell in column D.
I have reviewed other posts by searching counting highlighted cells, but I do not understand how to complete the entire process. One of the suggestion was to create a vb script in combination with using =cellindex(??). I do not really understand how to create this, name it and the run it.
I am trying to delete certain cells within a range of data (A13:E29) that have white font or #N/A's. The only information I need is the black data. I have to manually go and clear these cells for many different ranges. I'd like to incorporate a macro that analyzes the range first to delete the values that are white font or #N/A.
I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.
The code works, but it really slows my worksheet down when opening. Is there better way to write this?
Code:
Dim myRange As Range Dim cell As Range Set myRange = Range("V6:V50000") For Each cell In myRange If cell.Value < 2 Then cell.Font.ColorIndex = 5 If cell.Value < 1 Then cell.Font.ColorIndex = 3 Next
I'm trying to get the font to turn red in column G if it is greater than or equal to column I, and get it to turn orange if it is between 80% of I and I. This is what I've got so far.
Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("G" & Target.Row) >= Range("I" & Target.Row) Then Range("G" & Target.Row).Font.ColorIndex = 3 If Range("G" & Target.Row) >= Range(("I" & Target.Row) * 0.8) Then Range("G" & Target.Row).Font.Color = 45 End Sub
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 want the order size to be colored red. If the most recent order is larger than the previous order, I want the order size to be colored blue. If the most recent order is the exact same size as the previous order, I want the order to be colored black.
How would I make code that would let me "remember" the previous order size and the most recent order size, and let me compare them to conditionally format the font?
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?
Do While Cells(iCount, 5).Interior.ColorIndex <> xlColorIndexNone Cells(iCount, 5).Value = Cells(iCount, 5).Interior.ColorIndex iCount = 1 + iCount 'increment by 1 each loop Loop
End Sub
I get what the loop is doing but I am not sure what the Interior is referring to. When I looked it up I saw it can be a property or an object but I am not sure exactly what it means, why you need it and in what other cases you may use it? I am so confused about this I don't even know what to ask?
If I had to guess I think it means the the property of the individual cells but in that case I don't see where the object comes in?
I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description".
Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"
The example file explains it a lot better..
Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...
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 am using Office 2007 and have successfully used the Cells.Interior.Color to change cells fill color. I would like to be able to undo these changes. Depending on the data entered I am changing the color but I may need to change it back to white. When I use the Cells.Interior.Color = vbWhite the cell fill changes to white but the cell border is gone. Is there a way undo the color change and have the cell border show up?
I have some code that recently I found I had a new criteria that I originally did not have when I worked my original database. THis code works perfectly as is BUT if in column "D" if this is blank I would like to do nothing/ make no changes in that row.
Sub ChangeColorsotherdesigXXX() Dim c As Range
For Each c In Range("D2", Range("D65536").End(xlUp))
With Range("a" & c.Row & ":s" & c.Row) If Not CStr(c.Value) Like "612#" And Not CStr(c.Value) Like "712#" Then .Interior.ColorIndex = 36 .Font.ColorIndex = 1 End If
In Sheet1 cells A1-BW46 (3450 cells), using 9 different colors to fill all the cells.
In Sheet3, starting in A2, I have a plain text list of 3450 cell ranges "A1", "A2", "A3" etc covering all 3450 cells in Sheet1.
I want to populate next to this list the interior color value of the corresponding cell. So in cell A2, I will have the text "A1", in cell B2 I would have either a numerical or text value giving me the color that A1 in sheet1 is filled with.
The script doesn't have to auto update, I will run manually when neededI don't need any manipulation, only the value of the color, and that can be hex, rgb.
I can't upload the actual sheet I'm working with, but if my description of the problem is lacking, I may be able to create a sample sheet to give the idea.
Via conditional formatting, I am searching to change the color of a specific word in a cell (not the color of everything in the cell, but only that specific word - and the word repeats in the cell). A function such as =isnumber(search("NOK";A1)) colors all the cell values, which I do not want to do. How i can perform this task?
I have a B2:M13 range. I would like to find a way to find the next cell with Interior.ColorIndex = 1.
For example, if position is currently B2 (so myrange(1, 1)) and the next black colored cell is on B6 (so myrange(5, 1)), I would like store 5 and 1 into variables.
So if no black background is found after current cell on the same row, look for next black background on next row.
If current cell is in row-M (the last one ** the range), for example, and there's no black background following on this row, find first black background in row-B.
i have loads of oval shapes on a sheet and i am trying to loop through them when i open the workbook and change the interior color to red. however i'm getting an error and i can't figure out why?
