Posts by gonza

In cell A1 type your text Example:
HELLO

In Cell A3 type the fomula:

=(A1)

A3 is going to show: HELLO

Marcelo.

Chris,
The code from Dave work fine. and I just put these codes (desactivate password) in the beginning macro after the Sub() and the activate password in the end before the end Sub:
Example:
Your SUB() name macro
Sheet1.Unprotect Password:="Secret"

[your codes]

Sheet1.Protect Password:="Secret"

End sub ,of you macro name

Sorry because I did put this message in the wrong place but your recomendation work fine in my worksheet.
Thanks!!

Marcelo.

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)

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

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.

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.

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.

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.

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.

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

Thank you very much.

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.

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?