I want to be able to change the font color of the numbers in a column based on even or odd. I selected the columns and formated them to all be blue... thats the color I want for even, but now I can't figure out how to set a condition that checks for odd numbers and changes them to red. I now have a count of even/odd for all records, and even/odd for the last 30, this provided some very interesting results.
View 9 RepliesIs it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
I have five fields that have a conditional format applied to them: (see attached).
Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
i have a spreadsheet which is used to keep the training records for the company it counts no of staff and their skill levels for different parts of the job, when a person moves to a different branch the skill required changes and they have a set period of time to aquire the new skill.
when the time is up the colour of the old skill is changed to a red font by a conditional format based on the date.this is then counted by a calculation so the team know how many people are to be targeted for extra training.
the problem i have is the calculation will not count conditionally formated cells, so whenever i update the sheet i have to go to each cell with red text and change the font colour manually to red, this sounds really silly and is impossible to explain to anyone who does not understand conditional formatting. i e the people who will end up using the database
what im looking for is a way to count conditionally formatted cells based on the condition being met i.e. they are showing as red font.
I have a row of cells pertaining to jobs B3 across to I3.
In B3, C3, D3, E3 the value entered is text.
In F3, G3, H3, I3 the value entered is time.
What I am trying to do is to change the Background colour to Red on cells
F3, G3, H3, I3 if either H3 or I3 or are empty at the end of the current day but only if there is some text in any or all of the cells B3 - E3
So for example if H3 or I3 have no time put in them by 23:59 today, when the workbook is opened tomorrow the cells background colour will turn red.
Not sure if this if poossible or not? hopefully I have explained it well enough but if not pease let me know.
I have the following code. I am trying to conditional format a row based on the value of the A column of the row.I have a total of 3 rows. I have to use VBA because in time I may have 4 rows of data. The problem is I after the first do while loop the code stops and the code doesn't proceed to format row 2.
View 2 Replies View RelatedIm having some issues with some code Im working on. My goal is to delete the rows that do not have conditions met. Conditional formatting is used in 6 columns of my spreadsheet and the cell fill is to turn red when below .50 . This code is shown below. What I need to do now is look at the 6 columns where the conditions are applied, and delete any rows where the column cells are not red. To further clarify, if in row 1 column X:X does not meet criteria, but column AL:AL does, the row would not be deleted. However if none of the columns meet the criteria, the entire row is deleted.
View 6 Replies View RelatedWith the click of a toolbar button, I've got my vba inserting a new tab, renaming it, inserting headers and now working on the code to insert all the formulas needed in 12 columns to copy down as far as there is data.
The 4th column D is the hardest. Currently, I'm using manual "Conditional Formatting" to colorize the cells in Col D but need the VBA to place that "Conditional Formatting" code into the sheet so that when the sheet is created, it will hold the conditional formatting feature.
Specifically, the formulas I'm using in the Conditional format wizard are:
Formula: =$B2$D2 then turn the cell red Applies to: =$D$2:D
Copy down (in other words, the colorizing should work all the way down Column D as long as there is data in Col D.
How to write a VBA that will, if the value of a cell in column "U" is TRUE, that row's FONT (for the row the TRUE cell is in) will turn red.
View 4 Replies View RelatedI have a spreadsheet with due dates in column X. I want to create conditional formatting so that cells turn red when the due date is less than or equal to 30 days away, but turn gray when the due date has passed.
I've accomplished the first step by using the following: =AND($X6
I'm afraid as its a multiple conditional format and thus needs coding. At the moment I'm using the following
Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
Dim rng As Range
' Target is a range::therefore,it can be more than one cell
' For example,,someone could delete the contents of a range,
' or someone could enter an array..
Set rng = Intersect(Target, Range("E27:E34,G27:G34,I27:I34,K27:K34,M27:M34,O27:O34,Q27:Q34,S27:S34,U27:U34,W27:W34,Y27:Y34,AA27:AA34"))
If rng Is Nothing Then
Exit Sub
Else.....................
This works very nicely, so that for example if I type the number 10 into cell M33, that cells fill colour becomes black (and the text turns white). Alas I would now also like to extend the fill and text colour to the cell one to the left too. To take this example I would type 10 into cell M33 and cells L33 and M33 would both be filled black and their text would be white. I'm not sure if this requires a change to the code, or if its possible to just have a formula or something which copies the fomatting of one cell into another
Looking to use conditional formatting to highlight a cell based on the value of another.
I.E. Highlight Cell B2 if Cell A3 is 4 or greater.
I want to create a conditional format formula which is based on values in 2 cells.
Example:
Cell B1 = 100
Cell C1 = 50
I want a formule for cell A1 which turns red for exmaple when B1 is higher then 85 and Cell C1 is lower then 85. Only in this case the conditional format should work.
I have a monthly calender, with each month on its own sheet. I have a sheet named Holidays, which list the holidays. I have been able to use conditional format and highlight the dates in the months the holidays fall on, but I would like to highlight a range of cells below the date on monthly sheet. I have attached a sample of what I have and would like to achieve. I am not much good at using VBA, but would not be against using it either.
View 2 Replies View RelatedI have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.
View 9 Replies View RelatedPicture1.jpg
How do I create a CF if a cell is blank (in my case represent no sales) i want to have it yellow (ex. C2)
But if it has two consecutive blank cell i want to CF with Red. (Ex. F3)
But i also need to exclude blank cells when we we have no previos sales of that item (Ex.B3).
Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?
View 8 Replies View RelatedI am lookin to apply CF to a cell based on the value of the left two numbers in a concatenated formula. If the value of the left two numbers are greater than zero the apply the CF
View 6 Replies View RelatedI need contents in cell A5 to turn red based on 2 criteria occurring.
IFCell F5 is greater than 0 (zero) AND IF date in H5 is equal to or less than 30 from today's date. PS: if needed I have today's date in A1 for reference.
Can this be done and how?
I have two cells. both formatted to contain dates.
cell a1 will have a date in it. cell a2 will have a date calculated to be cell a1 + 42 days.
now, when cell a2 reaches a date that is greater than cell a1 + 42 i want cell a2 to be formatted as fill red.
so, i have tried a conditional format as =a1+42 >=TODAY() but it always highlights as red even if the value in cell a2 is less than cell a1+42.
I want to highlight an entire row based on the contents of of a particular cell. For instance, if c4 contains data, highlight the entire row 4. If c5 is blank, do not highlight the entire row 5...and on and on...
View 2 Replies View RelatedI have set 3 conditional formatting rules where the row from column A to K will change depending on the value on column L.
Selection is equal to A3:K100, conditions are similar to:
- Formula is =$K3="PIP"
Then for formatting I chose WHITE BOLD FONT & BLUE SOLID PATTERN, for example.
Conditional formatting only allows 3 conditions. If this can be replicated
using VBA, then you can set "n" conditions, correct?
what I am trying to do:
I have a workbook with 2 sheets.
Sheet "A" has a column called "FILEPATH" that contains the complete path to a file (eg. "C:mystuffdocumentsdocA.pdf"). There are about 3500 rows of filepaths on this sheet.
Sheet "B" has a column called "DELETED FILES" that contains a list of deleted filenames (eg. docA.pdf, docC.pdf, etc.), one filename in each cell.
I want to highlight the rows on Sheet "A" that contain a path to a file that is listed as deleted on Sheet "B".
I have a drawing register that contains drawing reference numbers in column D. When a new revision of a drawing is issued it is added to the bottom of the list. What I would like to do is any previous revisions to be greyed out and struck through.
The conditional formatting
= COUNTIF($D$1:$D$100,D1)>1
works well but strike through all duplicates, I need the most up to date left without the format until it is superceeded
i m trying to format a cell so that it colours in red when a specific text is chosen in another cell, ive tried conditional formatting but I cannot get it to work. e.g column A has a drop down list, if GB or IEN is chosen in that cell then cell in column K will become red. (as if these two are chosen then they have to input a number into column K). Once they have inputted the number into column K i want it to go back to its original format. Or if they dont select GB or IEN then it would stay in its original format aswell.
View 2 Replies View RelatedI'm attempting to write a conditional format formula that will change the background color based on multiple color options on in a row. I have cell A:c as first, last and middle name. I utilize the rest 40+ cells that provides dates that are conditionally formatted to based on dates. I've approached this trying to reference the columns dates and cannot figure it out. Heres what I would like to do:
If any cell in from D:Z has a background color of red, yellow, green, and none it will change the background color in cells A:C. Its easy to reference one cell but not multiple. I've attached a sample of what I would like to do and I manually changed to cells A:C to show how I would like to show my results.
Is it possible to format a row of cells (B7:KX7) depending on the value in column (G7:G1000) changing?
For example column G might have the following value entered into them.
A
A
A
B
B
B
C
C
C
So all the rows containing the letter A would be formatted one colour, then it would notice it changed to B and format all the rows with the letter B in, then C etc... The letters would always appear in blocks of 10 or more (representing students in a group) so it wouldn't go A,B,A,B,C,D,E
I am not bothered about the letter being tied to a certain colour, it just needs to change when the letter changes. I'd have no more than 10 different variables in this column, meaning no more than 10 different colours to format. Formatting would be black text with different coloured backgrounds.
In Column D of my work book are a list of months, I'm trying to use conditional formatting to do the following:
Highlight green the cells in Column D where the month is due (i.e the month is now).
Highlight orange the cells that are one month passed the due month.
Highlight red the cells that are two or more months passed the due month.
I'm using Excel 2007.
how to conditionally format the data in one table based on the criteria specified in another table on a different worksheet.
The Table I'm trying to format has a series of Products and Volumes that'll be available on different dates. Table 1.jpg
The Criteria table has each product and a corresponding Key Data. Table 2.jpg
All I want to be able to do is have the cell corresponding to the criteria to be highlighted but can't for the life of me figure out how to do it. If it makes a difference I'm working offAttachment 338501 a Mac. Excel Version 2011 14.4.3
Not very good at this but I have the basic sheet attached. All I want is a way to highlight the first cell when the date is within 30 days of the "Due date".
Calibration Record-example.xls