Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA Macro To Delete & Apply Conditional Formatting

I have a spreadsheet and there are about 30 columns that have several conditional formats set. I want to be able to have a macro that
1, clears all conditional formatting
2. Re establishes conditional formatting.

I have found that sometimes, the "record macro" works when I change/create a CF, but other times it does not.

Sometimes, when I select "manage rules" and have only 1 column selected, the macro records most of the columns CF, but other times it does not.

What am I missing?

Is there a way that I can select a range of cell, and then get the VBA code that would re-create the CFs set in those columns?

Weird...

Thanks
Mc


Post your answer or comment

comments powered by Disqus
How to remove Applied Conditional Formatting in a Copied Sheet(s) retaining Format?

Dear Forum,

I am creating several CopY sheets from a Parent WorkSheet and these Copied SHeets are without any Formulas but only have values and exactly the same formatting as the Parent WorKsheet...

I have managed to get the Copied Sheet with Values and the Formats but how do I remove the conditional formatting so that there are not formulas in the conditional formatting box as well as retain the formatting which was happening due to the Condtional Formatting..

I know it sounds contradictory however never know what all is possible?

If its possible then what code I need to add during the Copying Operation and also exactly where do I add it to not disturb the Conditional FOrmatting of the Parent WorkSheet.

Warm Regards
e4excel

Hi everybody,
I am working with quarterly sales data on which i have made dynamic range.
The data is arranged like following

Figures in Lac
A B C D
E F
Salesperson 1st qty 2nd qty 3rd qty 4th qty
Total Sale
David 50 70 60 10
190
John 80 30 50 20
180

Now I want to use conditional formatting using macro which will apply
conditional formatting each time I run the macro. But the problem is when I
am trying to record this macro I am unable to select the dynamic range. It is
easy selecting static named range but the main disadvantage is that I have to
change the range every time I add a new record. Is there any solution to this
problem? Please advice.

USD $10 by paypal vba code to delete rows in workbooks

Hi again brilliant forum

i am an excel novice

had some great work done by Wigi and Krishnu in the past

I have about 120 excel files (each has about 1 million rows and between 6 and 30 Columns).

the files are 200mb + so too big to upload

i want a vba macro to delete unwanted rows

is it possible to have a message or input box that i can enter up to 5 words and if any of these words are contained in any rows then i wish to keep these rows only (and if any of these words dont appear i wish to delete the whole row)

the macro will be deleting about 950 thousand rows and keeping about 50 thousand (hopefully).

i can do it very slowly with custom sort but if a macro can be done it will make life easier

Good luck if you fancy the challenge

Paul

Is it possible to use a macro to delete only cells formated in green ? In the
same spreadsheet I also have no color data, but the green data changes
weekly. I'd like to be able to run a macro that will identify which cells are
formatted in green, and delete only the data in those green cells. Would I
use/create a yes or no type column, yes being green, no being not green, and
then if cell x3 is yes, then delete data in cells a3:m3, if x3 is no, "". ??

Thanks,

Steve

Is it possible to use a macro to delete only cells formated in green ? In the
same spreadsheet I also have no color data, but the green data changes
weekly. I'd like to be able to run a macro that will identify which cells are
formatted in green, and delete only the data in those green cells. Would I
use/create a yes or no type column, yes being green, no being not green, and
then if cell x3 is yes, then delete data in cells a3:m3, if x3 is no, "". ??

Thanks,

Steve

Hello,

I am looking for VBA MAcros to clear contents of cells which having a formula with blank output. Every time i have to do this manually which becoming a headache for me every week.

I have 13 columns and everytime i have to use filter and select the blank cells and right click clear content. This procedure i have to 13 times. Every week the values is changing.

Cany anyone help me to solve this issue.

Hello,

I am trying to create a conditional format using VBA and apply that format to several rows in 2 columns. When I walk through the code the formula string contains the correct cell reference, however when the macro completes and I review the conditional format for the cells in the sheet the cell reference starts 9 rows and 1 column past what I want. I want the formula in cell A10 to be "=LEN(A10)>0" but when its executed I get "=LEN(A19)>0"
Below is the code I've created. (Note: wrkSht is a worksheet variable initialized to the correct sheet. This code is in a method inside of a class module)


	VB:
	
firstRow = 10 
firstCol = 1 
lastRow = 500 
lastCol = 3 
Set rng = wrkSht.Range(wrkSht.Cells(firstRow, firstCol), wrkSht.Cells(lastRow, lastCol)) 
rng.FormatConditions.Delete 
 
strFormula = "=LEN(A10)>0" 
rng.FormatConditions.Add Type:=xlExpression, Formula1:=strFormula 
rng.FormatConditions(1).Interior.ColorIndex = 24 
 
Set rng = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Dear Excel-ers,

I am new to this forum and completely new to Excel macros so I hope someone can advise me!

I have a worksheet consisting of 85 columns of reaction time data, each column representing a variable or condition.

I wish to highlight outliers - in this case, those that are more than 2.5 standard deviations away from the column mean.

So, under each column I've calculated the mean, standard deviation, and standard deviation x 2.5.

To highlight outliers in the first column I did the following:

1) selected the range of cases (a2 - a40)
2) applied conditional formatting (red type) if values were greater than the mean + sd*2.5 or less than the mean - sd*2.5 (the mean and sd*2.5 values are taken from the previously calculated values at the bottom of each column.

Which did the trick. However, as I have 85 columns and then over 30 workbooks with an identical format, it would be great to automate this process.

I've never used macros before in Excel, but I tried recording a macro while completing the above actions for a few columns:

    Range("A2:A40").Select
    Application.CutCopyMode = False
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=$A$45+$A$50"
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=$A$45-$A$50"
    Selection.FormatConditions(2).Font.ColorIndex = 3
    Range("B2:B40").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=$B$45+$B$50"
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=$B$45-$B$50"
    Selection.FormatConditions(2).Font.ColorIndex = 3
    Range("C2:C40").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=$C$45+$C$50"
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=$C$45-$C$50"
    Selection.FormatConditions(2).Font.ColorIndex = 3
End Sub
So, my question is, other than manually recording a macro and repeating this action for the entire 85 columns, or manually editing the code to apply to the different columns, is there a simpler way to achieve this? The range of cells I want to highlight is the same in each column. Also the position of the mean, sd and sd*2 is the same in each column. I rather suspect that they wouldn't be necessary at all to someone with a modicum of macro experience.

Any suggestions would be greatly appreciated. Many thanks for reading and do let me know if any further information is required.

I've run into a problem with VBA in Excel 2007 while working with conditional formatting. I need to apply a conditional formatting to a data range. However, the subroutine may be run again on the same set of data and I don't want to reapply the conditional formatting to the same data range if it already has the formatting applied. One approach would be simply to delete all conditional formatting on the entire data range, but there could be additional conditional formats which should remain in place. So I'm trying to find a way to cycle through all conditional formatting conditions to see if any of them match the one I'm wanting to apply. If any match what I'm wanting to apply, I'll just skip adding it again.

But I can't seem to find a way in vba to get the formula back out of excel.

Using:
Code:
Workbooks("testbook.xlsm").Sheets("sheet1").ListObjects("DataTable") _
  .DataBodyRange.FormatConditions(index).Interior.ColorIndex
I can have the fill color returned for a given format condition. But is there any way to return the formula in the format condition? I've tried:

Code:
Workbooks("testbook.xlsm").Sheets("sheet1").ListObjects("DataTable") _
  .DataBodyRange.FormatConditions(index).Formula1
but this seems to be only able to set a value, not return one.

Any ideas?

Thanks,

Will

I'm trying to find a macro to apply conditional formatting to a large number of cells, but not having any luck.

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.

Is this possible?

Please see attached file.

I am trying to do something tells me when a project (based on dates) is on track (green), delayed (yellow), Immediate Attention Required (red), and Closed (Grey). I managed to use built it conditional format to get the first three colors in the cells. When something is finished, the user will manually input ("C") for closed. Once all columns have a "C" in them, the project manager can choose to close the project. I have a macro that deletes the conditional format and color the row grey.

What I'd like to get help on is to figure out how to identify the rows that are Completed. If the Project manager chooses to close the project, VBA will find the row and color it grey. The number of projects will increase periodically, so I'd have to capture the dynamic range as well.

Your help on this is much appreciated.

Hi,
I was wondering if it's possible to apply conditional formatting to a cell or range through VBA code (i.e not through the excel menu bar) ? If it is possible any code or links would be much appreciated!

Cheers,
Sonia

ABCDEFGHI need to create a macro to perform some conditional formatting1DateDayTypeTimeConfirmed Order No.Site Location2SHEET OVERVIEW301/01/08TuesdayAB4CVertically there will be 366 tables to represent 366 days and Horizontally 10 tables to represent 10 employees,5Dwhich enables the work activities of 10 employees to assigned over the period of a year.6E7AAFIn the type box marked 'A' and 'AA' the user selects from a drop down box a parameter as list below left.8GOn entry of an 'n' in the type box the user can enter data into the 'time','confirmed order','site location' boxes.9HThe idea of having 2 x type boxes is to allow the day to be split into morning and afternoon.10I1102/01/08WednesdayMACRO / VBA FUNCTIONALLITY REQUIRED 1213In the 'type' boxes if the user selects anything other than 'n' then the 4 'site location' boxes and coloured and 14the appropriate text from the list below left is inserted into the 4 associated 'site location' boxes.1516Example:1718Box marked 'A' the user selects 'h'. Boxes marked 'BCDE' and filled with colour and the text holidayBox marked 'AA' the user selects 't'. Boxes marked 'FGHI' and filled with colour and the text trainingType List Entry ParametersWhen the user selects type 'n' after the associated boxes are returned to there blank state (no colour or text)nnormal dayhholidayThe macro/vba would have to respond on everytime a 'type' box changesssickttrainingThere would be 2 x type boxes per day, 366 days a year and for 10 employees. Therefore it would have to monitoruunauthorised absence2 x 366 x 10 (7320) type boxesbbank holidayccompany shutdown

I would like to apply conditional formatting if a cell has a particular function in it.

Example: If a cell has a 'vlookup' function in it, I want it to be formatted as currency.

If VBA is involved in the answer, that's fine.

Hello,

Apologies for the length of this post.

Information:
Using Excel 2002 on WinXP SP3. ** Please note: My organisation does not allow the use of the Analysis ToolPak so I am unable to utilise Networkdays or Workdays functions and would prefer a worksheet function rather than VBA.

Situation:
A new workbook to record KPI performance is created for each month There is a sheet (named Calendar) listing public holidays. Formulas have been added to calculate the observed day for each holiday. The observed date list is in Calendar!A2:A10 and this range has been named Holidays.

There is a separate sheet for each business day within the month. These record work on hand for each date entered into A11 down and compares these to B3 to calculate KPI compliance.

Problem:
I am trying to apply Conditional Formatting to B11 on each daily sheet based around 5 business days between 2 dates. I need to allow for weekends and public holidays which occur between the 2 dates. I have attached a sample workbook.
The work-on-hand date is day 0 when calculating the date difference5 business days must include the date in B3A11 contains the date of work on handB11 needs CF to indicate when the date difference between B3 and A11 exceeds 5 business days, equals 5 business days, or is less than 5 business days.
I have tried combining different functions (e.g. weekday, countif, datedif, sumproduct, etc) but am unable to get anything to work so I’m obviously on the wrong track or am putting them together incorrectly. I would greatly appreciate any assistance to help me put together a formula/formulas that work.

I have a resulting cell that will provide a computed value based on preceding cell entries. The possible entries are any cells in a 5X5 matrix, with values from 11 to 55.

This is a risk matrix, with 5 level of risk, which are nonlinear.

I want to have the cell color change with the value that is comuuted for the cell as follows.
low risk = 11,12,13,21,22 green
med-low risk = 14,23,24 dark green
med risk = 15,25,31,32,33,34,35 yellow
med high risk = 41,42,43,51,52 orange
high risk = 44,45,53,54,55 red

as you can see straight conditional formatting will not work, as values are not linear.

I tried to copy some vba code from this site, but I do not know how to debug, as it did not work.
it was: (i adjusted range to my column range, and colors to the colors i want, but none worked)

	VB:
	
 Range) 
    Dim icolor As Integer 
     
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 
        Select Case Target 
        Case 1 To 5 
            icolor = 6 
        Case 6 To 10 
            icolor = 12 
        Case 11 To 15 
            icolor = 7 
        Case 16 To 20 
            icolor = 53 
        Case 21 To 25 
            icolor = 15 
        Case 26 To 30 
            icolor = 42 
        Case Else 
             'Whatever
        End Select 
         
        Target.Interior.ColorIndex = icolor 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


hi guys.

I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.

My worksheet runs a lot of code, then deletes all macro code and saves itself.

The problem is the next time i open I still get the macro security warning!

I've checked thoroughly and there is definatley no vba left anywhere.

If I open the document, enable macros, and save it, then open it again, I no longer get the warning. verrry frustrating.

I have a spreadsheet (Excel 2003) that imports time data from another
application in the following formats displayed on the formula bar; for under
one hour, it displays mm:ss When the time is over one hour, it displays
h:mm:ss AM (or PM). When I apply conditional formatting, I can not get any
time over one hour to color code. I have been unsuccessful changing the
format of the imported data. Does anyone have a suggestion?

Hi,
I have a sheet for tracking vacation plan. In this sheet I want to make a
conditional formatting. The sheet contains three columns, the first one is
employee name, and the second one is last working day, the third one is
return to work. I want to apply specific conditional formatting on the three
columns by special conditional as follows:
If the last working day date is over due itself, the all columns to be
filled with color automatically.
Your help is highly appreciated

I need to apply conditional formatting using incremental addresses for cells
containing data to be compared for each cell across an 800-cell range. Is
there a way to do this without visiting each of the 800 contiguous cells
individually?

Thank you in advance.

Hello, is there anyway to apply conditional formatting to an entire workbook
in Excel 2003?

I saw a similar question that asked about applying conditional formatting to
a chart, and i tried applying that "technique" to a pivot chart, but it
didn't return the same results.

Does anyone know if it's possible to actually apply the conditional
formatting to a pivot chart?

Thanks!

Is this even possible? I've done conditional formatting to a cell based on
that same cell's info BUT what I want to do now is something like this...Can
I apply conditional formatting to cell A3 based on the information in cell
B3? If so, how do I do that?? I completely suck at writing formulas so, if
you know the answer, can you help me with how to write the formula, too?
Basically, I want to apply formatting to A3 if B3 has a value greater than
zero. Thank you!!

I don't agree with my boss's ideas regarding Excel Charts - I think that they
are to cluttered and become less effective. So, I would like to apply
conditional formatting via a checkbox to "turn off"(apply white/transparent
properties)to one or more series in a chart, effectively eliminating the
overlapping series from view temporarily. Can this be done? Is the answer
VB?I think this could be very effective in clarifying data that is
consistently overlapping each other. (Actually I would just make 3 separate
charts on the same page, but nobody is asking me!)
Tell me what you think...
Thanks,
Tim


No luck finding an answer? You could always try Google.