I would like to know the formula to highlight the color of text in particular cell if the value of the particular cell ( value is text ) is so and so.
For Ex: If the J6 is "Vacation" then the J1 text should be in red color.
I have an excel spread sheet & I wish to highlight any cell that contains any of the following characters with in a string of text.
/ : * " < > ? |
E.g. a cell in the spread sheet containing the text "Is this a Question?" would be highlighted.
I have tried to use - 'Format only cells that contain' > Specific Text > Containing > for each of the characters and this does not work.
When I do this any cell containing any text is highlighted.
In addition there is formatting on any cell > 60 characters which turns the cell red and this works fine.
What would happen if (when I get the problem above resolved), a cell is > than 60 characters & contains a character listed above?
Does 1 formatting take precedent over another?
(Not critical to know the answer to the additional question, just curious - as long as it is highlighted one colour or another then no problem.)
1 2 3 4 5 6
1
2 x x x x
3 x x
4 x x
5 x x
6 x x
Assume that is the table i have. In row 1 i need conditional formatting such that if any of the columns contain an x the first row should be color coded. In my above example every column in row 1 will be color coded expect for row 1 column 6 since there are no x's for any any row in column 6.
How can i do this in excel.Also instead of x's if i have manually entered color can we do the same ?
I have a spreadsheet that uses VB macros to calculate sums of cells based on the font color of the numbers inside. It used to be fairly easy going through each cell and "classifying" them by color, so that my macros can go ahead and sum the numbers in each respective color's cell... but now I have a huge amount of numbers and would like to automate the process somewhat. Here's an example using the A and B columns:
flight $400
hotel $150
hotel $130
meal $20
meal $15
flight $350
I tried using conditional formatting to automatically change the color of the adjacent cells based on the presence of a keyword such as "flight" or "hotel", but this change is only cosmetic, and doesn't actually change the font color (it is still the default black, hence why my color-summing macros won't work!).
I'm including a sample macro for what I use to color-sum my cells, but what I am looking to automate the color-coding process based on looking for keywords as explained above in my example. Here is one of the working color-summing macros (for red, in this case) if you'd like to use it as a reference:
Function SumRed(SelectedCells As Range)
' Adds the values of the cells where the font colour is red(3).
Dim Cell As Object
Dim x As Double
[Code] ...........
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?
View 1 Replies View RelatedWhat i want to do is highlight all row instances where that row column10 has the text "Roller" . All i can see is the forumular =$J1="Roller" ive tryed to select just the single cell J and tryed the whole row and a range. Though i get a error message about =-+ quotation ect.
View 5 Replies View RelatedI have data in columns A and B,
Column A has dates, column B has text
I am wanting to highlight text in column B in a colour
if date in column A is more than 10 days since date in column A on previous occurrence of text in column B
I have a workbook with 4 sheets. Each sheet is set up the same. There are 4 columns. The column headings are Title, Item #, 2009 Retail Price, and Price changed from 2008 (in A, B, C and D respectfully).
I need to find all red text in column C and place a 'Y' in column D. If the text in column C is not red I want a 'N' in column D.
I am working on a spreadsheet in Excel 2007 and i am trying to use a formula in the conditional formatting that will highlight a cell if the date is prior to today's date and the next column over is no. the formula i am using right now is =IF(F2>TODAY(),IF(G2="NO",TRUE,)). the problem i am having is that it is highlighting all cells that have a date and the next column over is no.
i can't figure out how to get it to do an if this, then that function correctly. one other thing that could potentially be causing my problem is that the cells.
I am trying to have highlight are calculated themselves based on a formula. see the attached spreadsheet for information.
Summary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
I want to write a macro for conditonal formatting
I want to highlight any cell containing the word "If "
See attached work book, it is an mot booking system that is all working fine except for one thing that I don't know how to change.
At the moment if you double click a cell in the test type column it enters the required data and turns the cell green highlighting the length of time and values this is all good but I am wanting the ones that start with n-side to turn yellow just so that we can see our own internal tests easily. It is control in the coding not formatting . Is there a way this can be done?
I'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.
View 9 Replies View RelatedI have a spreadsheet where I have a column of dates and I want conditional formatting to highlight the cell red if the date is less than or equal to today but if the cell is blank to do nothing.
At the moment I have the following formatting applied using the "format only cells that contain" option I have cell value less than or equal to =TODAY() except that obviously highlights every cell red that doesn't contain a date. Is there another conditional format I can apply in addition to this that will not highlight the blank cells ?
I have a spreadsheet that keeps track of staff training. in colum A is the date they completed the training. this training expires in 5 years time. i would like the cell to highlight red when the date has expired. and when i enter the new date in 5 yrs time the cell to return to no fill color.
View 8 Replies View RelatedI have 3 rows with numbers across several columns, each cell holding one number only. For example:
R1: 1101 - 1102 - 1103 - 1104 - etc.
R2: 2101 - 2102 - 2103 - 2104 - etc.
R3: 3101 - 3102 - 3103 - 3104 - etc.
A little further down I am having a list where the end user will write down the exact same number. Once the same number has been written into the list, I would like the number shown in the top rows (row 1 to 3) to be highlighted in green, basically telling the user that the number has been entered into the list below and is ok now.
How can I manage this via conditional formatting?
I need a macro that will highlight a row based on a cell criteria. I have used conditional formatting but there is a lot to format and has dramatically slowed the sheet down. However, I know nothing about macros/vbas.
E.g. If it states JI sent then I want the whole row to be highlighted light green.
App form needed = peach
Waiting list = light blue
Cancelled = red font strikethrough
DNA = purple font strikethrough.
Also - it could well be that in the row it has App form received, JI Sent and DNA all in 1 go. So ideally I would want that row to be highlighted light green with purple font strikethrough.
Booking Status
JI Status
Date JI Sent
Attendance
Cancellation date
Chargable / non chargable Cancellations
Chargeable / non chargeable DNA
Date taken
Time taken
Event Title
Start Date
Time
App form received
JI Sent
[Code] ......
In a column in which there are repeating numbers is there a formula I can enter into conditional formatting that will highlight a cell when the number doesn't match the value from the value above it?
So in this example the values with an * would be highlighted.
1*
1
1
2*
2
2
2
3*
4*
4
5*
5
I have a file where the cells are all formulas.
Employees are able to key in a number instead of using the number derived from the formula.
I want the cell to use conditional formatting without the use of macros, so it will highlight yellow if the cell is keyed in instead of a formula.
Is this possible? I have tried numerous things, but none seem to work.
Example:
Cell A6 has the number 24,248 derived from a formula.
Employee thinks the number should be 30,000 and keys that number in.
If it is 24,248, I want the number to have no fill, but if it is a keyed in number, I want it to be highlighted.
I am using the following formula on a conditional formatting to color cell A1 based on cell N5 date =$N5=TODAY()-2
The problem is that I need this formula to consider only weekdays, how can apply formula weekdays to my formula?
I am trying to write a VBA code to color cells.
In Column I
I need a formula to change the color of the cell based upon the following criteria
In cell I3, if J3>7 change color to teal, if H3<$A$1 color cell red, if J3<7 color cell yellow, if J3="COMPLETE" color cell grey, otherwise do nothing.
This is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
I want to conditionally format cells K8, L8 and M8 so that when cell C3 contains a 3 it causes the previously mentioned three cells to fill black.
I cannot seem to work out how to do this using the options available under conditional formatting. Is there another way?
Is there a way in conditional formatting to have a cell shaded a certain color based on the value of another cell in that same worksheet?
View 10 Replies View RelatedI would like to be able to put a value into a cell. By doing this I would like another cell next to it to turn a color. With that said, is it also possible to format a cell with several possible scenarios in one formula to be able to change the cell next to it a color?? Example is this lets say Cell B1 could have the letters, A B C D or E entered. So that the Cell A1 turns say the color Red.
View 2 Replies View RelatedI have 10 rows of 6 numbers, all between 1 & 49, in the range A1:F10. I also have the range A20:AW20, which are all currently blank cells. When I type a number between 1 and 49 inclusive into the range A20:AW20, any number that matches it in the range A1:F1 I want the cell to fill in colour.
View 1 Replies View RelatedIs it possible to use conditional formatting to to turn a cell red whenever the value in the cell is divisible by 10 ?
View 9 Replies View RelatedHow do I make my cell shade a certain color? For example, green if >90%, yellow if >70% and red if
View 6 Replies View Related 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 know how to set a conditional for a cell to change a color when i apply a specific value, but how about if i want to set E2 cell to change to green when i place a 'X' on F2?
Pretty much I want 2 columns that say Yes and the Other No. When i place a X on Yes that other cell turns green, if i place a X on No that other cell turns red.