You are here:

Excel/Highlighting the Cells


Hi Tom,

This macro works fine. It changes the color of cells in value fields when I change the threshold in I6 cell (wherever the total of employees given in employee column.)

But I have got one more condition to add in this macro. I have two threshold values 40 and 50. There will be heavy work season in which employees are supposed to work more than 40 hours so in that case 40 threshold should not highlight the cells which has more than 40 hours.

for example work season are :

9/16/2013 to 10/14/2013
9/7/2014 to 3/7/2014

Note: our year cycle is june to may. and week's data gets changed. every week as we present 20 week's data in the report.

I will email you the worksheet. Please help.


OK - email the worksheet, but give me a complete explanation.  Don't expect me to remember a question that was posted in the past that contains the details.  

Also, you say this macro, but there is no macro in your question.   so again, make sure you send all the information I need to answer the question.

I looked at your file.  You code has this structure:

If Sheets("Emp Schedule").range("I6").Value = 45 Then
ElseIf Sheets("Emp Schedule").range("I6").Value = 50 Then
ElseIf Sheets("Emp Schedule").range("I6").Value = 55 Then
ElseIf Sheets("Emp Schedule").range("I6").Value = 60 Then
ElseIf Sheets("Emp Schedule").range("I6").Value = 65 Then
ElseIf Sheets("Emp Schedule").range("I6").Value = 70 Then
End If

the coloring is done for each condition.  So if I6 = 50, then the code between 50 and 55 gets executed.  

ElseIf Sheets("Emp Schedule").range("I6").Value = 50 Then
For Each cell In MR
If cell.Value > 50 Then
With cell.Interior
       .Pattern = xlSolid
       .PatternColorIndex = xlAutomatic
       .Color = 16763955
       .TintAndShade = 0
       .PatternTintAndShade = 0
   End With
End If

ElseIf Sheets("Emp Schedule").range("I6").Value = 55 Then

Right now, if I6 was 40, it would meet none of the conditions and nothing would get colored; so I think it already does what you want.   (if you can only have 40 or 50, then I am not sure what the purpose of the tests for 55, 60, 65

If you want something different you would have to tell me in much more detail because right now the code seems to do what you have described.  I don't know anything about work periods.  You make the selection of the threshold, so I am not sure how work periods factor in.

Tom Ogilvy

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.