Apply Conditional Formats with code

  • I need a macro where need to check cells looking for letter. Depend of the letter is what color need to be assigned. I know how it work for one cell but how I can move the cursor to the next cells, like a loop?

  • Hi gonza,


    Following will give you a start:


    Dim rnArea as Range
    Dim rnCell as Range


    Set rnArea = Range("A1:A10")


    For Each rnCell in rnArea
    'Do Your stuff
    Next


    For larger ranges there exist much quicker approaches then looping through a range.


    Mail back if You need further help with above or needs another approach.


    Kind regards,
    Dennis

  • Thanks for your help.
    Is doing something but what I expect.
    this is are the codes that I have:
    Dim rnArea As range
    Dim rnCell As range
    Set rnArea = range("A1:B11")
    For Each rnCell In rnArea
    'Do Your stuff
    With Selection.Interior
    If rnCell = "R" Then
    .ColorIndex = 45
    .Pattern = xlSolid
    End If
    If rnCell = "Y" Then
    .ColorIndex = 20
    .Pattern = xlSolid
    End If
    End With



    Next


    I want the macro check the first cell and if the value of the cell is "R" is going to change of the color of the cell to red if is "Y" to yellow color. and then go to the next cell until they check all my range.
    I hope you know what I need.

  • gonzo,


    I guess something like this should be workable:


    Sub Check_Range_Value()
    Dim rnArea As Range
    Dim rnCell As Range


    Set rnArea = Range("A1:B11")


    For Each rnCell In rnArea
    Select Case rnCell.Value
    Case "R"
    rnCell.Interior.ColorIndex = 45
    Case "Y"
    rnCell.Interior.ColorIndex = 20
    End Select
    Next


    End Sub


    If the range You want to check changes from time to time You can use selection, i e:


    Set rnArea = Selection


    Kind regards,
    Dennis

  • Dennis, I have just a Little problem.
    The data from Worksheet is being updated using VLOOKUP from other spreadsheet. the key is the date and when there is not data found it shows #N/A and when i run the macro I got a run time error and debuging stoped in the option CASE, obuisly because there is not any letter. I did try to add CASE "#N/A" but it is not working. How I can control it if there is not data?


    Best Regards.


    Marcelo.

  • Hi Marcelo,


    One way is to check the status of each cell like the following:


    Sub Check_Range_Value()
    Dim rnArea As Range
    Dim rnCell As Range


    Set rnArea = Range("A1:B11")


    For Each rnCell In rnArea
    With rnCell
    If Not IsError(.Value) Then
    Select Case .Value
    Case "R"
    .Interior.ColorIndex = 45
    Case "Y"
    .Interior.ColorIndex = 20
    End Select
    End If
    End With
    Next


    End Sub


    Please note that a With-statement have been added for the variable rnCell.


    Kind regards,
    Dennis

  • Run time error solved. But it is posible to change the color to no color because It was data before and still in the color that it was before.
    Now in the same worksheet range I have cells with % values like %95 %96...etc. Can I use the same funtion CASE but this time comparing..
    if < 95% then RED COLOR.
    if >= 97 then Green.
    I got time values also in the range, like 10:00 and 12:00 and I want to do the same.
    If time<6:00 then red...etc.
    Any recommendation?



    Regards.


    Marcelo.

  • Hi Marcelo,


    Seems that we have here a moving target ;)


    Restore colour:


    Sub Restore_Range_Value()
    Dim rnArea As Range
    Dim rnCell As Range


    Set rnArea = Range("A1:B11")


    For Each rnCell In rnArea
    With rnCell
    If Not IsError(.Value) Then
    If Not .Interior.ColorIndex = 2 Then
    .Interior.ColorIndex = 2
    End If
    End If
    End With
    Next


    Question II:
    Yes and if You need more condition to evaluate You add more Case to the Select-Statement.


    Question III: Time


    Sub Check_Time_Range_Value()
    Dim rnArea As Range
    Dim rnCell As Range


    Set rnArea = Range("A1:B11")


    For Each rnCell In rnArea
    With rnCell
    On Error Resume Next
    Select Case .Value * 24
    Case 10
    .Interior.ColorIndex < 45
    Case 20
    .Interior.ColorIndex < 20
    End Select
    End With
    Next
    End Sub


    Kind regards,
    Dennis

  • Dennis:
    This is what I got now:
    Dim rnArea As Range
    Dim rnCell As Range


    Set rnArea = Range("B6:AA35")


    For Each rnCell In rnArea
    With rnCell
    If Not IsError(.Value) Then
    Select Case rnCell.Value
    Case "G"
    rnCell.Interior.ColorIndex = 35
    Case "Y"
    rnCell.Interior.ColorIndex = 36
    Case "R"
    rnCell.Interior.ColorIndex = 22
    End Select
    End If
    End With
    Next



    I do not know how I can transform this:


    IF VALUE IN CELL < 89% THEN
    RED ,I KNOW THIS
    IF NOT
    GREEN , I KNOW THIS ALSO


    IF VALUE IN CELL < 10:00
    RED
    IF NOT
    GREEN


    The problema is that I do not understand the last post that you put.


    Thanks for all your help.


    Marcelo.

  • Hi Marcelo,


    Following procedure seems to work:


    Sub Check_Range()
    Dim rnArea As Range
    Dim rnCell As Range


    Set rnArea = Range("A1:B11")


    For Each rnCell In rnArea
    With rnCell
    If Not IsError(.Value) Then
    Select Case .Value
    Case "G": .Interior.ColorIndex = 35
    Case "Y": .Interior.ColorIndex = 36
    Case "R": .Interior.ColorIndex = 22
    End Select

    If (.Value) &gt; 0 Then
    If .Value < 0.89 Then
    .Interior.ColorIndex = 3
    Else
    .Interior.ColorIndex = 10
    End If
    End If

    'Military time converted to decimaltime (1 day = 24 hour)
    If .NumberFormat = "h:mm" Then
    If .Value * 24 < 10 Then
    .Interior.ColorIndex = 10
    Else
    .Interior.ColorIndex = 3
    End If
    End If
    End If
    End With
    Next


    End Sub


    Kind regards,
    Dennis

  • Denni,
    Everything look pretty nice!!
    Now when I protected the worksheet and when I'm trying to run the macro it getting me an error because the protection.
    Is there any way to unprotect the worksheet from the macro and protect the worksheet again from the macro after the macro is done?
    Note: It is protected with a password.


    One more time thanks a lot!


    Marcelo.

  • Hi Marcelo,


    Here we we go with the final (?) solution:


    Option Explicit


    Sub Check_Range()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnArea As Range
    Dim rnCell As Range


    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Sheet1")


    With wsSheet
    Set rnArea = .Range("A1:B11")
    End With


    wsSheet.Unprotect Password:="Marcelo"


    For Each rnCell In rnArea
    With rnCell
    If Not IsError(.Value) Then
    Select Case .Value
    Case "G": .Interior.ColorIndex = 35
    Case "Y": .Interior.ColorIndex = 36
    Case "R": .Interior.ColorIndex = 22
    End Select


    If (.Value) &gt; 0 Then
    If .Value < 0.89 Then
    .Interior.ColorIndex = 3
    Else
    .Interior.ColorIndex = 10
    End If
    End If


    'Military time converted to decimaltime (1 day = 24 hour)
    If .NumberFormat = "h:mm" Then
    If .Value * 24 < 10 Then
    .Interior.ColorIndex = 10
    Else
    .Interior.ColorIndex = 3
    End If
    End If
    End If
    End With
    Next


    wsSheet.Protect Password:="Marcelo"


    End Sub


    Kind regards,
    Dennis

  • Dennis, I have a compilation error in this line:


    Dim wbbook As Workbook
    Dim wssheet As Worksheet
    Dim rnArea As Range
    Dim rnCell As Range
    '=============================
    'unprotect the worksheet
    '=============================
    With wssheet
    Set rnArea = .Range("A1:AA35") ,here is the compilation error.
    wssheet.protect Password:="xxx"
    End With

  • Hi Marcelo,


    Did You take a look on Dave's suggestions to secure the protection/unprotection?


    Anyway, it seems that You´re trying to protect the sheet instead of unprotect it.


    Here is the correct way:


    With wssheet
    Set rnArea = .Range("A1:AA35") ,here is the compilation error.
    wssheet.Unprotect Password:="xxx"
    End With


    And I presume that You have set the variable wssheet to the actual worksheet.


    BTW, please use only one thread for a question otherwise it will be confused and a overlap.


    Kind regards,
    Dennis

  • David,
    Sorry for my mistake.
    I sent a message to Dave and his information was very appreciated.
    Finally the password setup is working but I have an other problem.
    In this worksheet I'm using the option Data validation in one cell so after I select a new date from that cell is going to update the information because all the cells contail the VLOOKUP function. When I select the Buttom that is doing all the colors and I'm including the password protect worked very good, but the problem is this worksheet change if I select from the data validation, so, when I do that is telling me that the worksheet it is protected and it is not updating until I manually unprotec.
    so the question in this case is:
    Any way that when I select a new date or information from the data validation it unprotect the worksheet just enough to update the new data and after that protected again?
    Some caind of function who can be activated from that cell just to unprotect and protect the worksheet.


    Best regards.


    Marcelo.


    Edit:WillR[Yes] (abridged version)