Conditionally Formatting Range Of Cells Based On Formula?
Jan 18, 2013
I had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.
I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.
Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:
A: Person's name
B: Value of 1 or 0
The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"
I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.
1) How would I get conditional formatting working over a range
2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.
View 3 Replies
ADVERTISEMENT
Feb 5, 2013
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
View 1 Replies
View Related
Apr 18, 2012
Column A = list of names
O1:P20 MAY contain names
If A1 = Fred
AND O5, O16, P3, P19 = Fred
Selecting A1 changes all instances of "Fred" to RED-BOLD
If A2 = Felix
AND O9 = Felix
Selecting A2 changes "Felix" to RED-BOLD
Etc...
View 4 Replies
View Related
Jan 10, 2012
Is it possible to conditionally a set of cells based on a different cell? Example; Background color of cells A2:A10 = blue if A1>0
View 1 Replies
View Related
Apr 28, 2012
Is there a way to conditionally format a range of cells from VBA?
I want the cells to turn red yellow or green when i type into that cell r, y, or g.
I can easily select the range of cells, but dont know how to format them.
View 4 Replies
View Related
Sep 6, 2013
I have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.
View 4 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
Oct 22, 2013
what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?
View 6 Replies
View Related
Mar 12, 2014
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx‎
View 3 Replies
View Related
Dec 12, 2006
I have a workbook (attached) which has two sheets called “Team List” & “Masterfile Data Dump”.
TEAM LIST
This is the sheet that I want to populate when the data dump is a pasted into the sheet “Masterfile Data Dump”
MASTERFILE DATA DUMP
This sheet is where my data dump will be pasted to, columns A to Q and column R is a formula to work out the FTE
Calculation
The reason I want the list to auto populate is that it will change week on week and is a time consuming task keep updating it. I want to be able to type a Team Leaders Name into cell F2 and it populate the list of the agents in that persons team Cells F4:G18 (Name & Surname) along with there FTE (full time equivalent) in Cells I4:I18.
The problem I have is I have no idea how to calculate the FTE sum as the data dump can have one to five shift patterns (rows) for one agent (See my example on the “Master file Data Dump” Sheet of Joe Bloggs. /I have tried to use a VLOOKUP which I have used but this will only return the first value or first rows value of that agents name.
View 4 Replies
View Related
Oct 21, 2011
I am building a graph and i want to conditionally format it so that if a bar is less than 24 it wil be green and if it is greater than 24 it will be red. how to do this?
View 1 Replies
View Related
May 16, 2009
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................
View 4 Replies
View Related
Dec 22, 2005
I have 7 rows in the same column of type percentage. I'd like to turn
the text of the highest valued cell red. How exactly is that done with
contional formatting (vs. IF statements for each cell)?
View 10 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
Jan 5, 2009
I need to write a code that will take a range of cells and combine them into one cell in a different sheet.
The problem is, the cell range is defined by formatting - there is a top border and a bottom border, and that's the only way to really define the range.
View 9 Replies
View Related
Jan 26, 2007
I'm trying to format a range of cells based on a single cell. Below is the code that
I am using:
Private Sub Worksheet_Change(ByVal Target As Range) ...
View 3 Replies
View Related
Jan 10, 2014
I have created a speadsheet for a tournament that lists the team names down column A (leaving a heading row). I have a formula in the top row which copies each row heading to a column heading 2 columns wide (to be able to record a result)
Formula: =INDIRECT(ADDRESS(ROUNDUP((COLUMN()+1)/2,0),1))
This way it will be possible to keep the results of the tournament on a grid. I would like to put in conditional formatting that meets 3 criteria...
1, it must fill every second row but only fill cells that have column and row headings (ie. rows that will have content in them which will be determined by the number of entrants). eg. if i have 10 teams then every odd row from row 1 to 11 will be filled. If 40 teams register then every odd row from 1 to 41 will be filled. 2, the fill needs to only fill cells that have column and row headings eg. if 10 teams register then every odd row from column 1 to 21 will be filled. Then 3, If the row heading and column heading are equal then it need to gray out or fill a different colour (or lock cell contents to empty if possible) but again, only fill cells that have column and row headings.
I have 2 rules.
Rule 1: =(ADDRESS(1,EVEN(COLUMN()-1)))=INDIRECT(ADDRESS(ROW(),1)) PROBLEM:
It needs to only fill cells that have a column and row heading which it doesn't, it fills the correct cells untill there is no column heading then fills every other cell in columns beyond the last column with a heading.
Rule 2: so far this works:
=AND(MOD(ROW(),2)>0)
but when i try to restrict it to cells with row and column headings it doesn't work. This is what I had:
=AND(MOD(ROW(),2)>0,IsEmpty(INDIRECT(ADDRESS(ROW(1),COLUMN()))"")=FALSE) OR =AND(MOD(ROW(),2)>0,CELL("contents""",ADDRESS(ROW(1),COLUMN())))
View 1 Replies
View Related
Mar 22, 2007
I have two sheets (Results and August) with numbers in column A, I want to change the color of the cell on Results if its contents matches the number on August. I was feeling adventurous today, so I tried writing some VBA code, but it changes the color of ALL the cells. What am I missing?
Sub FindMatches()
'Compares student numbers on Results sheet to those on August sheet; if match is found then highlights the student number
Dim Sht1Rng As Range
Dim Sht2Rng As Range
Set Sht1Rng = Worksheets("Results").Range("A1", Worksheets("Results").Range("A65536").End(xlUp))
Set Sht2Rng = Worksheets("August").Range("A1", Worksheets("August").Range("A65536").End(xlUp))
Set d = Nothing
For Each C In Sht1Rng
Set d = Sht2Rng. Find(C.Value, LookIn:=xlValues)
If Not d Is Nothing Then
Sht1Rng.Interior.ColorIndex = 10
Set d = Nothing
End If
Next C
End Sub
I suspect that I shouldn't be using sht1rng.Interior.Colorindex, as I think that may color the entire range? What should I use instead?
View 8 Replies
View Related
Jul 18, 2014
I'm looking for a way to apply conditional formatting to a dynamic range, as the number of rows will vary each week.
Col A could be used as a reference, as it will always have a number in it, but wherever it stops is where I would need the conditionally formatted range to cut-off as well.
The actual range that would need formatting applied to it is Columns F-I where:
For any values < 1.00 (it should also cover blanks, which should be treated as zeros), red highlighting is applied & For any text that reads "Flagged" or "Not Reported", they would need to become bold
Additionally, I would like to start the formatting on Row 2 of the sheet in question, named "1010version"
View 3 Replies
View Related
Feb 19, 2010
My attached spreadsheet is for tracking when certain tasks were completed on each case and figuring the number of days that it takes to complete these tasks.
The task I am needing help in formatting is the Corrections Received field (column E). We need to track the cases that do not have corrections received within 7 days of the changes being requested (Out for Corrections, column D). The issue is that there would not usually be a date entered in the Corrections Received column when we need to look at those cases...because they would not be received yet. In order to not get an extreme default value (like the -40181 or 40922), I entered the =Now() function in column E (except for first case as an example of what this would look like otherwise).
What I was hoping to be able to do is use conditional formatting to look for that Now() formula. If the formula is present, the cell would be highlighted. If a date were entered, even if it was today's date, the fill on that cell would be removed. That way we could tell when the displayed date is a true date and when it is a placeholder until we receive the information.
Currently, it either highlights no matter what the cell includes or it doesn't highlight anything...
View 3 Replies
View Related
Feb 5, 2010
I want to create a 'virtual switch' so that if I change one cell from 1 to 0, that a formula in another cell becomes active. I can't just have the formula cell * the 1/0 cell, I need to comment out the formula or it draws in too much data.
View 5 Replies
View Related
Feb 28, 2014
What I am trying to do is create a multi-currency expense report. For example, cell B1 has a drop down list of three different currencies. Based on the currency selected in B1, I want C1 to reflect that number formatting using the proper currency.
Im sure there is an easy way to do this but I have tried using an if statement in the conditional formatting section but it does not work.
how to total the different currencies using the ifsum formula, so this is more of a presentation task, but still it has annoyed me to no end.
In the example workbook, the currency value selected in row E should effect the currency formatting in Rows F-L
View 1 Replies
View Related
Apr 29, 2009
i've been trying for teh last two days to figuire out a way to conditionally format the first cell in a row to turn a certain color based on the values of several independant cells in that row, as opposed to monitoring the whole row. I have tried numerous IF, AND, OR, formulas to no avail. The cell obviously also needs to ignore blank cells...Can this be done?
Basically I want the first cell to turn yellow if dates in certain cells are within 90 days of today, and turn red if within 30 days of today.......
View 9 Replies
View Related
Sep 29, 2006
I'd like to hide rows based on the value of a specific cell (that specific cell will always be C14, and it will have up to 8 variables selectable from a drop down list).
For example, if C14 = Innovative/exploratory, i'd like to hide rows 15-45. If C14 = Technical Service, i'd like to hide rows 15-32, and 42-44. I'm not familiar with VB, but am a quick study -
View 4 Replies
View Related
Nov 22, 2007
I am trying to hide rows based on the contents of column BS. It the cell is empty (not just zero value, but completely empty of all formulae), then I want to leave it unhidden, then if the cell value is less than the value in cell E2 I want to hide the row. This code doesnt seem to like the first if...then next i
Sub hiderows()
Dim i As Long
Set v = cell.Value("$E$2")
' Turn off screen refresh for speed
Application. ScreenUpdating = False
' Get last row Col BS
For i = Range("BS2000").End(xlUp).Row To 15 Step -1
If IsEmpty(Cells(i)) Then Next i
ElseIf Cells.Value(i) < v Then Rows(i).Hidden = True
End If
Next i
' Turn on screen refresh
Application.ScreenUpdating = True
End Sub
View 4 Replies
View Related
Feb 6, 2008
I'm trying to figure out how to lock a range of cells (all in a row) when some puts a specific character in one of the cells. I have a sheet where I want people to enter data. Once they put a 'C' in the last column to indicate that the entry is complete, the whole row should be locked so they can't change any of the cells for that entry. There are some good pointers already on the forum to help me get the basics:
Conditional Cell Lock Unlock
http://www.ozgrid.com/forum/showthread.php?t=66397
http://www.ozgrid.com/forum/showthread.php?t=76379
However, all of these point to specific cells rather than a range of cells that can be located close to the target of the change.
View 3 Replies
View Related
Nov 26, 2013
Attached is a workbook. I have manually highlighted the cells to give idea of what I would like the outcome to be.
Moonah OPS sheet.
In Row 5(Shift times), under the drop down menu in cells B5:J5, there is a range of Shift times.
And for the purpose of this I have selected
B5 as 0800:1621, manually highlighted B11:B27
C5 as 06:00, manually highlighted C7:C24
D5 as 07:00:1521, manually highlighted B11:B27
E5:I55(RDO, REC, SICK, Travel) can remain as manual input if it is to much to work out.
Is it possible or just a pipe dream, to use some method to automatically highlight a range of cells based on the C5:J5 'time'?
If the time is an actual shift time, the colour will be green and dark green writing as default, then we can allocate jobs/training/meetings and manually colour them.
Daysheet DAY MONTH YEAR template - Highlight.xlsx‎
View 7 Replies
View Related
May 20, 2009
I am working with the Conditional Formatting, which is fine for one cell. Here is what I am trying to do: IF cell in $A1 = 1 then bold $B2:$M2 and apply solid line border to top of cell ranges. I have tried conditional formatting but it only formats the cells in column A. And I can't seem to find a BOLD statement for the cell formulas.
View 2 Replies
View Related
Jul 30, 2014
I have a range of cells where each row relates to an employee.
In Cells (Columns H:L) I want the range of cells on that row to turn blue if the user puts a "H" in one of the cells.
Also it will need to be adapted for "O" , "Y", "X" - each with different colours.
This needs to be repeated on each row for each person.
View 5 Replies
View Related
