VBA Conditional Formatting - Change Font Color across row per 1st column (6 colors)

It is easily done if we knew a few more specifics. Can you attach one more example with the colors manually set for 1 or 2 names?

I would code it to trigger off the change event for columns A, F, M, and T. I am just not sure what colors would go where. I am guessing maybe replace the yellow color?

I put in a macro that gets you started.

I only did Sunday,Monday,Tuesday and Name1,Name2,Name3. You can finish the rest, and modify it accordingly.

Look in Worksheet_Change() in the Sheet1 Module.

Roy (and all) --

Thanks so much! That helps a great deal, better than anything else I've managed to try.

Two things I still need help with:

1) Some of the columns in the date block do not use data validation. Some are the result of formulas, and some are just input from the user.

2) It seems that if I have data continuous across several date blocks (i.e., all the columns have something in them), if I change one of the names, everything contiguous to the right changes color, even in adjacent date blocks.

I guess that's why I was thinking I needed a solution that addressed particular named ranges.

Thanks so much!

P.S. My last post was particularly in reference to Roy's solution.

I'm also wondering if there's a way I can change the font color of JUST the employee initials column, and then set a regular conditional format condition for the other columns to COPY the format color from whatever is in the first column?

Would that be simpler?

Thanks!

John

I guess you didn't look at the workbook I upladed.
So, I'll post my partial solution here.
I've improved it so that it takes the color from Name1, Name2, and copies across the block.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lColor As Long, lCol As Long
    
    Select Case Target.Column
        Case Range("Sunday").Column, Range("Monday").Column, Range("Tuesday").Column
            Select Case Target.Value
                Case Range("Name1").Value
                    'lColor = 1
                    lColor = Range("Name1").Interior.ColorIndex
                Case Range("Name2").Value
                    'lColor = 3
                    lColor = Range("Name2").Interior.ColorIndex
                Case Range("Name3").Value
                    'lColor = 4
                    lColor = Range("Name3").Interior.ColorIndex
        End Select
        For lCol = Target.Column + 1 To Target.Column + 4
            Cells(Target.Row, lCol).Interior.ColorIndex = lColor
        Next lCol
    End Select
End Sub

Foxguy --

Thank you! Yes, I did look at yours as well, but it changed the interior of the cell, rather than the font. I'll give your code a try as well and see if I can change it for font color.

Thank you!

John

The only other problem I'm running into is that the working copy of the sheet is protected, so users can only enter data in the unlocked cells. When I try use it on a protected sheet, I get 'Runtime error 1004' saying that it can't be done on a protected sheet.

If you use a macro to protect the sheet, you can add the "userinterfaceonly:=True" capability. Then your macros can change protected cells, but the users can't.

this code protects all worksheets so that your macros can change protected cells, but the users can't.

Private Sub Workbook_Open()
    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Worksheets
         sh.Protect userinterfaceonly:=True
    Next sh
End Sub

Foxguy --

I pasted the code and changed Interior.ColorIndex to Font.ColorIndex. I get the message:

Run-time error '1004'
Method 'Range' of object '_Worksheet' failed.

Thanks for any assistance you can provide.

John

What line did it crash on?

scratch this message.

What version of Excel are you using?

Foxguy --

It indicates the crash at:
Case Range("Name1").Value

Thanks!

Excel 2003

That's indicating that there is no Range("Name1") on the activeworksheet. Did you accidentally delete Range Name "Name1"?

Foxguy --

Sorry about that ... obviously there are more details that are relevant than I realized.

The range 'Name1' is on another sheet in the same workbook.

John

put lines at the top:

dim sh as worksheet
set sh = worksheets("xxxx")

where xxxx is the name of sheet where "Name1" is.

and change the line to:

Case sh.Range("Name1").value

and obviously all the rest of the relevent lines.

I switch it FALSE for the procedure for clearing the calendar -- otherwise it clears my formulas and everything.

Then I switch it back to TRUE at the end of the clearing macro. At least I think that's what I'm doing -- I know before I added that, I was wiping out everything with this macro.

Ok, I think I have it.

At the top of the macro put:

If Target.cells.count > 1 then exit sub

That's a neat trick for clearing unlocked cells. It seems a little dangerous to me, but I'll have to remember it.

Should I put that bit of code at the top of the macro, below, the opening, like this:

Sub emptyUnlocked()
If Target.Cells.Count > 1 Then Exit Sub
   If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo Then
   Exit Sub

Doing it that way gets me a Run-time error 404 "Object required"

Also, do you know of a safer, better way to clear the data input/unlocked cells in my calendar?

Thanks so very much

Originally Posted by jds217

Also, do you know of a safer, better way to clear the data input/unlocked cells in my calendar?

I think I would cycle through all the cells specifically looking for unlocked cells.

Dim lRow as Long, lCol as Long

for lRow = 1 to sh.usedrange.rows.count
    for lCol = 1 to sh.usedrange.columns.count
        with sh.cells(lRow,lCol)
            if not .Locked then
                .value = ""
                ' or .clearcontents
            end if
        end with
    Next lCol
Next lRow

this would probably take a little longer, but it's a lot safer. And the speed probably wouldn't be noticable.

I meant in the "Worksheet_Change() macro.

That way when your EmptyUnlocked() macro runs, my macro won't interfere with it.

There is another solution which would cover any future changes.

Sub emptyUnlocked()
    application.EnableEvents = false
    ..... your lines
    
    application.EnableEvents = true
End Sub

Then NO other macros will run while your's is running.

Scratch - wrong thread

OK, yes, I moved the code to the Worksheet_Change() macro, and now everything is executing without any errors.

However, the color-coding isn't working. I get different colors for the first column for the first 3 days, and just in the first week, but it doesn't carry across the other columns in the date block.

I'm attaching the actual spreadsheet, if that would make it clearer.

And thank you very, very much for all the help!

John

One other thing I've discovered ... when I first open the file, anytime I try to change an employee, I get an error message:

Unable to set the ColorIndex property of the Font class.

However, once I run the macro to clear the calendar, then I don't get that error message anymore.

Still having the same trouble with the colors.

Originally Posted by jds217

One other thing I've discovered ... when I first open the file, anytime I try to change an employee, I get an error message:

Unable to set the ColorIndex property of the Font class.

However, once I run the macro to clear the calendar, then I don't get that error message anymore.

the userinterfaceonly property can not be stored when the workbook closes. It has to be set EVERY time you open the workbook.

You need to run this when the workbook opens

Private Sub Workbook_Open()
    dim sh as worksheet

    for each sh in thisworkbook.worksheets
        sh.protect userinterface:=true
    next sh
End Sub

Then in emptyUnlocked you can replace

    For Each sh In ThisWorkbook.Worksheets
         sh.Protect userinterfaceonly:=True
    Next sh

with

    ActiveSheet.Protect userinterfaceonly:=True

You need to set the font color in Names on Sheet1. That's where the Macro picks up the color to copy across the data block.

Also, If you want it to change the font color in "Sunday", "Monday", etc.
change the line from

For lCol = Target.Column + 1 To Target.Column + 5

to

For lCol = Target.Column  To Target.Column + 5

I changed it in the attached file.
Look at the range Name "Names" on sheet1. Just change the font color to whatever you want for each person, and it will update the color on the calendar sheet the next time you select that name.

Yes, beautiful and brilliant!!!! Thank you!

One last thing I need help with ... the LAST column in each day, Monday-Saturday (Sunday is different, requires less data) -- columns S, Z, AG, AN, AU, BB -- is a user input cell, not a drop-down list, and the color formatting isn't working right for just those cells.

Thanks!!!!

John

And trying to figure out how to set up a macro to on opening ... thought I had it, but I get a "Compile Error: Named argument not found" on the

Private Sub Workbook_Open()

line

Foxguy --

Thank you so much ... I've worked with the file, and got the opening macro to work ... very happy about that. I'm going to see if I can get that last column to color format correctly.

Foxguy ... YES ... I tried to decipher the code, and figured out how to fix that last column. Perfect solution!!! Thanks for all your help!!

John

Just got back.

I am curious as to that compile error. Did you figure out what argument was not found?

I assume you figured out to put Sub Workbook_Open() in the ThisWorkbook module.

I assume you figured out the line should be :

For lCol = Target.Column To Target.Column + 6

I guess I'm not a good counter

Foxguy -- Yes, I tried to examine the code and figure out which part was the "number of columns to change", and just changed it to 6, and it worked perfectly. Thank you very much. I learned a few things in the process, and now am committed to getting a book from the library on learning more VBA code so I can take ideas I find online and tweak them.

Roy, thank you for your input as well. I think part of the difficulties I was having was that my original spreadsheet was more complicated than the fragment I'd posted, and I didn't realize how those differences were affecting the results. With much (greatly appreciated) correspondence between Foxguy and myself, he was able to help me hammer out a solution that worked.

John