Conditional Format: Alternate Row Shading Based On First To Letters In The A Column
Oct 27, 2008
I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.
The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.
For instance:
Column A
CPST4
CPST5
FLADS3EC
FLADS2EC
FIL4
CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.
MacOffice Excel 08 is used so no VB code can be used.
View 9 Replies
ADVERTISEMENT
May 21, 2008
In a workbook I have
Sheet 1 and Sheet 1a
1a is used to provide a start and finish date for a task - there are many of them
The following is located in a cell in sheet 1 which looks at 1a for the dates etc
HTML =IF(AND($A$427+B$430-1>='1a'!E10,$A$427+B$430-1<='1a'!I10,B$429<>"S"),"X","")
The end result is an X located in the respective feilds.
This works fine but.
I have set up the Calendar area in 1 so that when the X is shown it is Red.
The sheet also shows a Blank in both the SS [Sat and Sun]
What I would like to do is to have instead of the blank in the SS [Sat & Sun] a shaded grey which would clearly indicate that this was weekend.
What I am after is the weekdays with the X as now and the weekend only in a grey shade as it is at the moment without the X
View 14 Replies
View Related
Aug 17, 2009
I have a sheet that highlight cells to show that they need to still be filled out if another cell in the same row has a number in it. What happens is that when you put the number in the row the cells won't become shaded or partially shade until the workbook is saved.. Maybe I can put some code in that tells the work book to update whenever a cell is changed?
View 3 Replies
View Related
Mar 5, 2014
I have a file that contains multiple rows of data. I built a macro that will then hide rows with a $0 balance. When the macro is completed, my spreadsheet is only showing the lines with a $ amount. (The $0 rows are hidden)
What I'd like to do is then format the unhidden rows so that each alternate row is color coded. This will separate the rows visually so I can more easily follow the rows across to view $'s per line.
I've attached a sample spreadsheet (it does contain the macro to hide the $0 rows). How can I then format the remaining rows with alternate shading?
View 4 Replies
View Related
Jul 28, 2014
How do I shade a whole row based on the value in one of the columns?
View 11 Replies
View Related
Oct 24, 2007
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 Related
Jun 27, 2013
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.
View 1 Replies
View Related
Nov 22, 2013
I need to make an entire column formatless if the cell in row 3 contains no text. I want the whole column to just appear like a normal formatless column, but if there is text in the row 3 cell, I want it to be the way I have it formatted right now.
View 1 Replies
View Related
Jan 29, 2014
Wondering if it is possible to conditional format cells in "Jan"-"Dec" columns based on "Next" column data?
What I need is (lets say fill colour to differ) in one cell per row, based on latest input and "next" data.
Month Avg
Date
Jan
Feb
Mar
Apr
May
Jun
Jul
[code]......
So for example:
row2: May is latest input so need fill in cell +2("next" column data) from may. In this case July
row3: Feb is latest imput and "next" also 2. Result should be Apr
row8:May is latest input, "next" is 6. Res should be Nov.
Finally, only one cell per row to be highlighted so need to overwrite colour fill when new data input in a cell already colour filled.
View 9 Replies
View Related
Jan 18, 2008
I have 64 squares that will produce some repeat letter sequences. How do I make it so that When there are repeats (multiple instances of the same result) they turn the cell blue or whatever. I would like to specify again that there will be may more than one repeated sequence so its not as easy as I thought it would be so like the 1st set could be clue and the next set would be green or whatever.
View 14 Replies
View Related
Jul 11, 2014
I have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.
View 2 Replies
View Related
Mar 20, 2013
Is 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???
View 3 Replies
View Related
Aug 18, 2006
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
View 10 Replies
View Related
Sep 19, 2013
I am using Excel 2010 on XP. I would like to apply conditional formatting to shade every group in alternating color. For example, I have a list of US states. I have a formula already to produce this:
Current Conditional Formatting Formula: =MOD(SUM(1/COUNTIF($a$2:$a2,$a$2:$a2)),2)=1
STATES (unfiltered)
AL
AL
AK
AK
AK
AR
AR
AZ
AZ
AZ
When I apply a filter, the rows remain shaded as they were originally:
STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ
I want the conditional format to change when I have filtered out items (DESIRED RESULT):
STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ
I would assume SUBTOTAL(3,...) would need to be incorported into the conditional formatting formula above, but I do not know how.
View 2 Replies
View Related
Jun 16, 2014
I have a row of numbers that change as they go up. I will have 2 rows of numbers that will range from 1's all the way to 9's (B through BO) I will have different letter sequences that I will be entering as the criteria in column F either on the Up and Down sheet or the Even and Odd sheet. When I say different sequences. This is 1 group of letters. The next group of letters in column F will be in a different order. They will always be a U and D in one and E and O in the other but in row 13 it may have a U or D and it will be the same all the way down. The object is for me to be able to place the letters in either column F from rows 13 to 60, place 2 rows of numbers in rows 58 and 59, press the advance cell in G1 and it it populates all across the spreadsheet from I to BO. I will get all different number sequences since I am starting with a different number in rows 58 and 59. I have all the letters color coded and they move up based upon the color of the letters in row 1. If there is a green E in column M then I will be the column that the number advances from 1111 to 2111. If there is a green O then column K will advance up from 1111 to 1121.
I made a short video if you wish to view it.
The conditional formatting is very important so I need to have the green font with gray shade and red font with yellow shade [URL]....
View 2 Replies
View Related
Oct 30, 2007
I'm trying to update a series of different sheets in a book based on what month I have filled in data for. For example, at September Month End, I will fill in data vertically. Now, I have different tabs that reference certain cells in that column. There row number never changes, but once a month I have to go through and increase their column letters by 1, and there are a lot of places to update. Can anyone lend me some ideas or a solution to how I should go about using formula/macro to update these cells.
View 9 Replies
View Related
Jul 28, 2006
change the colours of alternate rows in order to make reading line by line easier. I know that I have used this before as a Conditional action ie:- if(mod...... etc
As always, the program I used it in has 'disappeared' so I can't check back with that.
View 5 Replies
View Related
Feb 9, 2010
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.
View 2 Replies
View Related
Jul 24, 2012
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
View 3 Replies
View Related
Apr 23, 2007
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 Replies
View Related
May 30, 2007
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.
View 13 Replies
View Related
Nov 6, 2008
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.
View 14 Replies
View Related
Feb 9, 2010
Im 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 Related
Jan 17, 2014
With 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.
View 2 Replies
View Related
Jul 23, 2014
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 Related
Mar 17, 2007
I 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
View 9 Replies
View Related
Feb 19, 2007
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
View 2 Replies
View Related
Dec 26, 2013
I need a macro that will clear the contents of all cells shaded white in range c1:c20.
Clear the contents, leave the cells shaded white.
View 1 Replies
View Related
Jul 24, 2013
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.
View 2 Replies
View Related
Apr 9, 2009
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.
View 8 Replies
View Related
