Apply Conditional Formats with code

gonza · Feb 1st 2003

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?

XL-Dennis · Feb 1st 2003

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

gonza · Feb 1st 2003

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

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.

XL-Dennis · Feb 1st 2003

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

gonza · Feb 1st 2003

You are the man!!!!!!!
Work perfectly.

Thank you very much.

XL-Dennis · Feb 1st 2003

Gonza,

You´re welcome - glad it worked

Kind regards,
Dennis

gonza · Feb 1st 2003

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.

XL-Dennis · Feb 1st 2003

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

gonza · Feb 1st 2003

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.

XL-Dennis · Feb 1st 2003

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

gonza · Feb 1st 2003

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.

XL-Dennis · Feb 2nd 2003

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) > 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

gonza · Feb 2nd 2003

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.

XL-Dennis · Feb 2nd 2003

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) > 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

XL-Dennis · Feb 2nd 2003

Since we can´t edit here comes a small corrections:

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

wsSheet.Unprotect Password:="Marcelo"

should be:
With wsSheet
Set rnArea = .Range("A1:B11")
.Unprotect Password:="Marcelo"
End With

Kind regards,
Dennis

gonza · Feb 2nd 2003

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

XL-Dennis · Feb 2nd 2003

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

gonza · Feb 3rd 2003

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)

Apply Conditional Formats with code

Participate now!

Share