• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Criteria for Conditonal formatting in VBA

S

slohman

Member
I have a worksheet with Columns A:M it needs to find if a multiple condition is met and colour B:M, First search is if Column B has the words "Dial before you Dig" and if Column J has a date (any date) I then need it to automatically change B:M and colour lets say red.

I then need it to look in Column B for "Installation", "Security" etc there might be 20 of these searches but Column J will always be a date.

I need it to be if both a met not just one criteria.

I though a macro would be better suited as I am copying and pasting from another worksheet on a daily basis. So each time I open up the worksheet it maybe delete all fill conditions and starts again with new from the macro, I dont know of any otherway.

Also the rows are moved around depending on the date in J1. It is sorted in which job should commence first.

Thanks In Advance
 

Attachments

  • sample sheet.xlsx
    11.3 KB · Views: 2
Slohman

Your data file doesn't support your comments above at all?

Column B is Item and so is unlikely to have things like Installation, Security etc
Column J is a Rep/Contractor not a date

I believe that simple Conditional Formatting will suffice for your requirements

Can you please repost a file with example of any and all conditions that you require as well as non compliant examples
 
I thought a macro would be better suited as I am copying and pasting from another worksheet on a daily basis so each time I open up the worksheet it maybe delete all fill conditions and starts again with new conditions from the macro, I don't know of any other way.

Also the rows are moved around depending on the date in J1 as to when the job will be book in
 

Attachments

  • CostSchedule.xlsx
    17.2 KB · Views: 6
This is the code I'm using to copy and paste rows into Cost Schedule but when I paste the rows it does have any conditional formattting maybe this code needs to be changed.

Code: 
Sub copyCount()
    Const FILEPATH = "C:\Users\Arcon Welding-PC\Documents\"
    Const FILENAME = "CostSchedule.xlsx"
    Const SHEETNAME = "Work Schedule"
    Dim wbMaster As Workbook, newBook As Workbook
    Dim sht As Worksheet
    Dim fname As String
   
    Application.ScreenUpdating = False
    Set wbMaster = ThisWorkbook
    Set sht = Worksheets("Work Schedule")
   
    If Application.WorksheetFunction.CountA(sht.Range("$A:$B")) > 5 Then
      Application.DisplayAlerts = False
      Set newBook = Workbooks.Open(FILEPATH & FILENAME, True, False)
      lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 3
      wbMaster.Activate
     
     
      Worksheets(sht.Name).Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Resize(, 13).Copy
      newBook.Worksheets(SHEETNAME).Range("A" & lastrow).PasteSpecial xlPasteAll

      Application.CutCopyMode = False
   
    End If
   
   
    Set wbMaster = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub
 
Hi, slhoman!
Besides the last uploaded file, consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.
In this case it's missing the .xlsm file that holds the posted macro (worksheets, values, etc.).
Regards!
 
Ok lets try again from the start. I have a Workbook (called Cost Schedule.xlsm) with a Worksheet (called Work Schedule) I need many conditional formatting rules and I update this sheet on a daily basis. I thought running a macro on open or exit to see if the criteria is meet and it would change the colours of the Column A-M. I need it to be a AND IF Statement to find "Dial before you Dig" in Col B also a date in Col J and any text in Col M.
 

Attachments

  • CostSchedule.xlsm
    13.3 KB · Views: 7
Hi, slohman!

I'm afraid that I can't understand your problem yet. Your uploaded file now it's a macro enabled .xlsm file instead of a non-macro .xlsx file, but it still hasn't any VBA code. The macro that you posted it obviously should be placed at another workbook since it's opening your uploaded file. However that's not the problem, I guess.

I've done this: created a new workbook, placed your posted code into a standard module, entered a few (more then 5) cells in columns A:B and run the macro. The result is that all the rows in the newly created file with the VBA code were copied into the uploaded CostSchedule.xlsm file, 3 rows below the last used row, in this case paste occurred at line 10. Despite of the fact that 8 in advance were garbage only, either if I deleted them or not the paste action was correctly done, but the conditional format were not applied to other than row 10. Why? Because the CF in the uploaded file were set only up to row 10.

So you have 2 choices:
a) Set up the CF to more rows, enough to handle the copy and paste daily actions.
b) Modify your code changing this part as follows:
Code: 
      With newBook.Worksheets(SHEETNAME)
        .Range("A" & lastrow).PasteSpecial xlPasteAll
        .Parent.Activate
        .Range("A" & lastrow).Select
        .Rows("3:3").Copy
        .Rows(lastrow & ":" & Cells(lastrow, 1).End(xlDown).Row).PasteSpecial _
            Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      End With
*Code maintained as much as possible as the original.

Regards!
 
Hi, slohman!
In the code that you posted earlier, replace the lines from "newBook.Worksheets( ..." to "... .PasteSpecial ..." for the snippet of my previous post. Then proceed as you're actually doing.
Regards!
 
That works terrific but I have at least 30 conditional formats and I believe it makes my workbook very large. Is there no way to put the conditional formatting into a macro that runs after the above code.
 
I have tried to macro record with no luck and tried to add my conditional formatting with no luck

All the conditional formatting help I see on all the sites don't seem to help with what I want

I looked at the site above and tried to do it myself

I started with this target.FormatConditions.Delete

but I need to find out how to write

=if(and(Column A has the word "Dial")and(Column F has the word "Booked")and(Column J has a date in it I need the Columns from Row A:M to turn Blue.

I then have about 30 more conditions to add to the macro.

I can run this macro anytime even on open or close or save.
 
You must log in or register to reply here.
Back
Top