How To Apply Conditional Format To A Range With VBA
Feb 27, 2012
I recorded a macro in which I applied a conditional format to a range (the selection).But, when i replay this macro, the conditional format is only applied to the first cell in the range. This can be seen in the "Manage Rules" dialog of Conditional formatting, where the "applies to" column shows only one cell, even though a range of cells was selected by the code.
I set a breakpoint at the first line of code here, and confirmed that the selection is correct, and indeed it is, as in the immediate window, ?Selection.Address correctly gives "$O$6:$O$21".
I have a column in a sheet referencing data from a column in another sheet in my workbook. I want to apply conditional formatting on these numeric values, from red for lowest vals to green for highest. However the fact that my column is referencing cells that will not yet have data means that they are being pulled over as 0. I am not displaying zero values in cells but the zero vals are still being included in the conditional format. Is there any way to discount these zero vals?
I would like to fill in the same conditional format down to 51 other rows but the formatting for each row should be based on the contents of its own cell i.e. $D$3, $D$4, $D$5 etc. How can I do this with code?
I was wondering if is possible to apply a conditional format rule to a range of cells only when certain other cells outside of the range are the active cells. eg row C4-J4 has a conditional format to identify duplicates from range C5-C10, but I also want the same conditional rule to apply separately and uniquely if the active cell falls within the next active range D5-D10. So basically only apply conditional formatting separately and uniquely as the active cell moves its way across the columns?
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
I have used VBA to apply conditional formating to a range of cells. i.e if cell B14 <>"" then row 14 is pale blue untill cell V14 is populated with the time then it removes the formating. One criteria is that if Q14(21/08/06 20:00)>NOW(AA2) and < NOW+1(AA3) then Row turns green(i.e. is due in the next 24 hrs).
Problem is that this formula is applied after a field is updated. When 21/08/06 20:00 comes and goes the row remains green unless I update one of the fields along that row. What would I have to do to make the formating change back automatically when the critera is no longer being met. Here is my codethere are 2 other if statements similar to this with in this code but this is the only part that shouldn't require any user input for the formatting to occour)
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B13:AP162" On Error Goto ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Me.Cells(.Row, "AP").Value = "YES" And _ Me.Cells(.Row, "Y").Value <> "" Then Me.Cells(.Row, "B").Resize(, 30).Interior.ColorIndex = 43 'mad green Else End If End With..................
I am trying to use conditional formatting to basically unhide a named range when a specific text is input into a range of cells from C23:C32. I have taken the named range "Screw_Test" and changed the formatting to make it hidden. White text on white background, that sort of thing. What I would like is to change the background color and text color back to something visible when the following is met. In C23:C32 I have a drop down. When Pedicle screw is picked in any of the afore mentioned cells, it would trigger the conditional formatting that would change the "Screw_Test" range (D22:F32) as a whole, making it visible. I haven't had much luck.
I've been trying to get around the 3 rule limit for conditional formatting, and I've found the code I need on the site
I've changed it to suit my needs
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then Select Case Target Case Is = "Test" icolor = 6 Case Is = "Test2" icolor = 12 Case Is = "Test3" icolor = 7 Case Is = "Test4" icolor = 53 Case Is = "Test5" icolor = 15 Case Is = "Test6" icolor = 42 Case Else End Select
Target.Interior.ColorIndex = icolor
This works great for one cell, but I need to format a row range based on the single cell. So if Test is entered into A1, then it changes to yellow, I also need the next 6 cells on the row to change too- In this case [B1:G1].
But it needs to cover the range in the code A1:A2000
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.
Hello, we have a stop light spreadsheet for status with several different projects. In each row there are conditional format status green - good, yellow - needs work, red - bad...based on data from another tab.
There is an "overall status" column that pulls the worst color that exists for a given row.
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.
I have a spreadsheet that keeps track of the equipment we have on rent. Whenever we have a new rental, I insert a row and enter the information. I have several conditional formats I am applying - (1) making the font a light grey so that it is hard to see on any rental that has been called off, but invoicing isn't complete on, (2) making the font red on any item that is within 3 days of the term, and highlighting any row that isn't showing an invoice in over 45 days. When anything is completed (rental has been called off, and the final invoice has been approved), I cut the row out and put it in a sheet entitled "Closed Equipment". When I cut out closed records or add new records, it will occasionally mess up my conditional formatting by only referencing one row. Additionally, I don't want the conditional formatting to transfer over to the "Closed Equipment" tab.
Would this be easier to keep clean and straight if I used VBA? Or should I just continue to occasionally check my conditional formatting and clean it up? I thought if I used a Named Range instead of cell references, it might work better, but entering named range "On_Rent" converts to the cell range ($A$4:$AA$194).
I am using Windows 8, Excel 2013. The file is on a network drive, and others in the office can look at it (they all run Windows 7, and either Excel 2010 or 2007), but they look at it so seldom.
conditional format formula that is based on a text range within a cell. In my case, I would like to compare the low and high range when inputted as a number followed by the quotation marks followed by space then hyphen, space then number and ending in quotation marks. The quotation mark is being used to represent inches.
Like this in cell D14: 0.2” - 2.2”
I am using the following condition format formula which works for the strict case above.
HTML =OR(D16<LEFT($D$14,3)+0,D16>MID($D$14,8,3)+0) However, there will be times when users using this spreadsheet may leave out the spaces on either side of the hyphen, or add more than one space between the number and hyphen. Also if additional digits are added, my formula above does not include all the digits in the results. I prefer to leave the quotation marks in...........
I need to check a range of cells for 2 different values. If either value is present, I need to shade a cell (outside the original range) red. If neither value is present I need to shade the cell yellow. Can this be accomplished with conditional formatting?
I am trying to create a macro (which will go in an add-in, using Excel 2007) which will apply a custom format to any selected cells which have their formulae hidden (Format Cells, Protection, Hidden). A similar macro works fine for locked cells.
Here is the UDF I wrote, which returns True/False based on the Hidden status of a cell:
VB: Public Function Hidden(Check_Cell As Range) Hidden = Check_Cell.FormulaHidden = True End Function
just place all three pieces of code into a module, and change the first line of the two macros to a standard "Sub Macro1()" type format.
1. Where there is data in column B 2. Goto first row of data 2. If column J = "Closed" 3. then select Cells from column B to J in that row and colour these cells in red 4. If column J= "Open" 5. the select Celss from column B to J in that row and colour these cells in grey 4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
=============================================================== Sub Colourclosed() Sheets("Risks").Select Dim LastRow As Long Dim i As Long LastRow = Range("B" & Rows.Count).End(xlUp).Row For i = 8 To LastRow If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select Selection.Interior.ColorIndex = 3
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
I have a workbook that contains 168 sheets of data (it's an extract from a PM tool) which is effectively a status report from each project in our portfolio. Contained within each status report are some financial data that shows a Plan number and a Forecast number for which I want to apply conditional formatting to this section (this is the same section for each sheet), to all the 168 sheets without having to go individually into each sheet. I have searched here and all the varying responses to a similar situation as mine, do not cater for the number of sheets that I have. And I need to do this on a monthly basis at monthend. So in the example below I want to apply conditional formatting if the Forecast (Cols D & G) are greater than Plan (Cols B & E). Is there a way of doing this just with the conditional formatting or would it need a VBA script?
Col A Col B Col C Col D Col E Col F Col G Financial Summary - Selected Project Currency: USD
My conditional formatting code works fine for one column; however, when I try to apply the conditional formatting to two columns, the code only applies to the first column listed. Additionally, I have two sets of "rules" that apply to the same column. One of the "rules" includes two columns. The other rule applies to only one column. I'm not sure if this is my issue, or whether I'm trying to apply the same code to two different columns.
I'm trying to find a macro to apply conditional formatting to a large number of cells,
What I would like to do is when cell E96 has a value of a, cells E3:F95 are shaded in grey. Then when cell G96 has a value of a, cells G3:H96 are shaded in grey, and so on down to IU96 having a value of a and cells IU3:IV96 shaded in grey.
I have created an excel file that contains a lot of worksheets which contain a lot of raw data and charts. There are 5 different tabs for 5 different companies. Each tab currently contains only 1 year of raw data for that company. Then I have another excel worksheet that has nothing but charts on it. Each chart is a bar chart that shows each of the 5 companies. It also shows the current rate for the month for each company and the 12 month average for each company. On the chart there are also 3 lines. There is a standard line that we try to meet each month. There is also a line for the upper control limit and a line for the lower control limit. Here is my problem:
The 2 bar charts (current rate for the month & the 12 month average) are created and updated by the raw data in each of the 5 data sheets.
Based upon the values in each of the cells that create and update the bars on the charts themselves: I want the bars to be RED if the values are below the lower control limit.
And I want the bars to be GOLD if the values are above the upper control limit.
And I want the bars to be green if the values are in between the upper and lower control limits.
I have a column filled with time data that I want to copy in to a column formatted to a time format (hh:mm:ss), the thing is when i copy the data to it the format does not apply unless i double click each of the brackets, there are 14000 lines so doing it manually is a hastle to say the least, is there a way to apply the time format immediately?