Conditional Formate VBA Based On Other Cells
Jul 17, 2007
I am trying to convert the following macro to a Worksheet (section change) format, but cannot figure out how to have the cells B87:M150 change based upon the text (red, green, white, amber, na) in O87:o150.
Sub ordinate()
Dim r As Range
For Each r In Range("o87:o150")
If r.Value = "RED" Then
r.EntireRow.Interior.ColorIndex = 3
ElseIf r.Value = "AMBER" Then
r.EntireRow.Interior.ColorIndex = 44
ElseIf r.Value = "WHITE" Then
r.EntireRow.Interior.ColorIndex = 2
ElseIf r.Value = "GREEN" Then
r.EntireRow.Interior.ColorIndex = 4
ElseIf r.Value = "NONE" Then
r.EntireRow.Interior.ColorIndex = 2
ElseIf r.Value = "NA" Then
r.EntireRow.Interior.ColorIndex = 15
End If
Next
Range("N86:IV132").Interior.ColorIndex = 2
Range("A86:A132").Interior.ColorIndex = 2
End Sub
View 9 Replies
ADVERTISEMENT
Feb 14, 2010
Using 2003, I'm trying to do the following, data starting in A3, and ending in L3:-
- If A3 is blank, cells have no format
- If A3 has a value, with A4 blank, fill is light blue and bottom line solid, top line dotted
- If A3 has a value, and A4 does too, fill is light blue, top and bottom line dotted
- If H3, which is a date field, is greater than today font is black, if less than today, font is red
View 9 Replies
View Related
Nov 17, 2011
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
View 5 Replies
View Related
May 3, 2013
What I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
View 5 Replies
View Related
Mar 11, 2008
what would I do if I want cell B:1 to be formatted a certain way if the value in A:1 is a certain value?
View 9 Replies
View Related
Aug 18, 2014
On the attached worksheet I'm looking for the squares in F4:F6 to be coloured corresponding to the colour names in D4:D6 using conditional formatting. I just can't get it to work today.
View 1 Replies
View Related
Mar 30, 2014
I have a worksheet which has the years 2001 to 2013 on in row1. I am trying to find a way of highlighting the entire row in green if a specific year contains an "a" (Marlett, tick). The complex part is that I would like the specific year to be determined by an entry input into cell A2 on sheet 2. See Attachment for reference.
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
Sep 17, 2009
I am using conditional formatting to make cell background's go red if the data satisfys some criteria
Now I want to add the values in those red cells, can anyone make a suggestion as to how I can do this.
View 6 Replies
View Related
Oct 18, 2013
Picture1.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).
View 3 Replies
View Related
Nov 14, 2013
I have created a table that has working hours of staff members over many weeks. Week number as column headings (1 to 52) and staff name as Row headings. E.g a row may be
John Smith, 37, 37, 37, 37, 64 (commas to show seperate cells)
How would I go about using conditional formatting so that the formatting changes according to the sum of the values in each pair of cells?
I need to add the total hours of every two weeks for some staff and change the fill colour of both cells accordingly to highlight which weeks staff have worked too many/few hours.
So (B1+C1) would be a pair, the total would decide which fill colour is used on both B1 and C1, and then (D1+E1) would be the next pair and so on.
I have tried using 'a formula to determine which cells to format' and placing =(B1 + C1) = 74 and making it fill the cells green but this appears to be doing (B1+C1) as the first pair and then (C1+D1) as the second and changing the format for the first cell only.
View 7 Replies
View Related
Feb 6, 2014
In the past I have used VBA script to change column with size based on cell values as per follows:
[Code]....
Now I wish to change row size in a similar fashion only the criteria will be a maths function rather than a string in a single cell. The maths function will simply be a sum of cells in a row. I will need to test whether or not the row ads up to a number greater than zero.
To illustrate what I am trying to achieve I have (poorly) written out a rough attempt at the code below:
[Code] ....
I am unsure how the syntax works for this, I considered defining the range as a string first so I could test the value of a single variable (by defined name) rather than directly referencing the row of cells in the formula. As this code needs to be repeated many time for many rows the simplest solution would be b.
View 7 Replies
View Related
Sep 19, 2012
I'd like to format (colour fill) C1 when H1 contains the word "Bills"
I've tried to "use a formula to determine which cells to format" but it keeps using absolute references ($H$1).
Ideally I'd like to apply the "format" to the entire column C.
I did think I'd be able to use OFFSET but it's not working.
View 1 Replies
View Related
Feb 15, 2010
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
View 2 Replies
View Related
Apr 27, 2007
I have a worksheet that I've applied conditional formatting to which works very nicely apart from when I use the formatting and apply it to a cell based on the contents of another.
What I don't understand is when I put a formula in cell b1 to read =$a1="yes" and format accordingly it works. So when I type in "YES" in to cell a1 ,cell b1 is highlighted. When I type in "NO" the B1 cell isn't highlighted which is what i would expect. IF however I delete what ever is in cell A1 the cell B1 is still highlighted.
View 8 Replies
View Related
Jun 11, 2009
I have a list that looks like this:
1. Here I have som formatted text.
2. Here too.
10. And here.
110. As well as here.
The content of each line is in a single cell.
How do I go about automatically chopping off the number, the dot and the space before the text, but retaining all formattings?
View 9 Replies
View Related
Aug 13, 2009
working on a sheet I have one col that is formated for dates (yyyy-mm-dd) this is fine if you enter a date from the 1900's or 2000's but enter a date from the 1800's and it's a no go!
example:
Enter 14 jul 1941 and it formats 1941-07-14 no problem
Enter 14 jul 1841 and you get nothing just 14 jul 1841 no formating at all. And this is the same for all the date formats.
View 2 Replies
View Related
Jul 7, 2008
I'm using Excel 2000 and I have downloaded a report from another system. The dates come in for example in the format 10.06.2008. Using the replace functionality I can change this to 10/06/2008. However, when I do this using VBA
Range("L49:L300").Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
it works on most but some of the dates get switched to 06/10/2008.
View 9 Replies
View Related
Apr 10, 2014
I would like to use conditional formatting to mark cells with arrows depending on the difference between the values in those cells and figures from different cells. Namely, I have percentage values in column A and B. I need cells in column B to be marked with arrows (up, right or down) when the difference between value B and A is bigger than 2%, bigger than 0 but smaller than 2%, and smaller than 0 respectively (see the spreadsheet attached).
View 2 Replies
View Related
Jan 8, 2014
I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".
View 2 Replies
View Related
Jun 3, 2014
I have a matrix in excel to show % and $ for multiple columns - set up like this:
Rows Part 1 Part 2 Part 3
Criteria 1 % $ % $ % $
Criteria 2
Criteria 3
the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.
View 6 Replies
View Related
May 23, 2013
The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.
View 2 Replies
View Related
Feb 8, 2007
hi i have a formula in a module that show a date as such
"ddd d" and this shows up as say "fri 6"
how can i get it to show up "f 6" or maybe "fr 6" and not have to use the whole three letters
:o
View 9 Replies
View Related
Apr 30, 2008
I'd like to import some dates into some textboxes on a userform.
I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:
1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)
View 9 Replies
View Related
Jul 19, 2012
I have a gantt chart that was built in excel. I need to shade cells in the timeline based on persons title under each project.
I can provide a snip of what the layout looks like for a better visual. I want the PM's Green, Supers Yellow and Persons Reporting Orange.
To get the green bar shown is using formula: =SEARCH("Project Management",$E17) , The issue with this is it fails to consider the start finish dates. For this particular project it works but when the formatting is "dragged down" the issue arises.
View 9 Replies
View Related
May 4, 2014
I am looking for a method to conditionally format a variable number of cells below a single cell into which data is entered.
For example ABC = 6, DEF = 12. Therefore, if I enter ABC into D1 then D1:D6 should be conditionally formatted.
View 3 Replies
View Related
Jun 12, 2009
A1:A6 have numeric values 1 to 6.
I want the conditional format values to change Based on the value in Cell B2.
If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.
IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.
I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
View 6 Replies
View Related
Aug 28, 2009
I have created a drop down box that allows me to choose multiple different Text Options. Example: Cell B4 has dropdown that lets me choose the Text Options, "House", "Car" and "Truck".
Next, I have 5 or so other columns underneath with their own text. What I want to do is have different cells highlight themselves depending on what is in Cell B4.
Example, if "House" is selected in B4, I want cells B7 and B9 to highlight. If "Car" is selected, I want cells B7, B8, and B10 to highlight. If "Truck" is highlighted, I want cells B9-B11 to highlight. Is this possible to do? I've tried using If/Then statements, but those don't seem to work for this type of thing.
View 3 Replies
View Related
Sep 14, 2009
I'm trying to get it so that a cell becomes automatically highlighted if 2 other cells conditions are met. For example, I want cell A5 to become highlighted if BOTH cell B1=Loan 1 AND cell B5=Yes. Here is a mockup of what I'm talking about incase it makes it easier to understand. I'm using Excel 2007.
View 3 Replies
View Related
Jul 28, 2013
I have a spreadsheet where I want to be able to Fill cells green in Column B to Column N for any Rows that have a value in Column L. All other Rows should remain the current colour (orange).
I can Fill the cells in Column L using conditional formatting but am not sure if I can use Conditional Formatting on other Columns so that their Fill colour is determined by the value in Column L?
View 3 Replies
View Related
