Conditional formatting (interior cell color) with VBA

[Jmoz092]

I'm trying to modify the code found in this thread for a workbook I have. I've added the formulas that suit my conditional formatting requirements, but I need help/education with applying the rules to each row on the worksheet (2-31) and also modifying the sheet name(red lettering) reference in two of the rules (the sheet name is dynamic).

[COLOR=#454545][FONT=&quot][SIZE=2]Private Sub Worksheet_Change(ByVal Target As Range)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]
[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]Dim thecell As Range[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]
[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]For Each thecell In Target[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    If (Not IsEmpty(thecell)) Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        With thecell[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            If .Value = " =and($I$5= ""Yes"", $K$5=""n/a"",MIN([COLOR=#ff0000]'Data 201707001'[/COLOR]!E49:E68)=0)" Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ' red[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                thecell.Interior.Color = RGB(252, 13, 27)

[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ElseIf .Value = " =OR($I$5=""Yes"", $K$5=""No"")" Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ' yellow[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                .Interior.Color = RGB(255, 253, 56)

[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ElseIf .Value = " =[COLOR=#ff0000]'Data 201707001'[/COLOR]!$I$34>45" Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ' green[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                 .Interior.Color = RGB(205, 254, 204)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            Else

[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ' white[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                .Interior.Color = RGB(255, 255, 255)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            End If[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        End With[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    Else[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    'white[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        thecell.Interior.Color = RGB(255, 255, 255)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    End If[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]Next[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]End Sub[/SIZE][/FONT][/COLOR]

 

[mumps]

Re: Conditional formatting (interior cell color) with VBA help

In which column is the target? Is "Yes" always in column I and is "n/a" always in column K? Can I assume that the row numbers will correspond to the target cell row number? Can you explain in more detail how the sheet reference is dynamic and will the E49:E68 always be the same.

 

[Jmoz092]

Re: Conditional formatting (interior cell color) with VBA help

Column I is "Yes" or "No"
Column K is "Yes", "No", or "n/a"

I was intending for the target to be the worksheet, as in any changes to the worksheet cause this macro to run. But, The user will make changes to cells in columns I, and K for this project...so if the target needs to be those columns, I guess that's ok.

Row numbers are 2-31 and are a -1 from the dynamic sheet number/name. The sheet names are dynamic insofar that they're concatenated from year, month, and case number within the workbook (2017, 11, 001). There are 30 cases per month. Via other macros, this info is elicited from the user and entered into a worksheet of the workbook. Cases are numbered from 001 to 030. Within each case's worksheet, cells E49:E68 will always be there. That range will or will not be filled with info pertinent to the conditional formatting query. We're mainly concerned with whether or not there's a minimum value of 0 in that range or not.

 

[mumps]

Re: Conditional formatting (interior cell color) with VBA help

First of all you have to decide what your target range is because that will determine when the macro is triggered and it will also determine how references to cells are made. I think it would be problematic if your range were to be the entire worksheet. I still don't follow how you want to refer to the dynamic sheets. I assume that the macro will refer to one particular sheet on one particular run and then to another sheet on a different run. How do you decide which sheet name the macro should use on any particular run?

 

[Jmoz092]

Re: Conditional formatting (interior cell color) with VBA help

I was thinking to maybe use an array for the sheet names like so:

[COLOR=#454545][FONT=&quot]sheetName(0) = Sheet2.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(1) = Sheet4.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(2) = Sheet6.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(3) = Sheet8.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(4) = Sheet10.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(5) = Sheet12.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(6) = Sheet14.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(7) = Sheet16.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(8) = Sheet18.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(9) = Sheet20.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(10) = Sheet22.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(11) = Sheet24.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(12) = Sheet26.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(13) = Sheet28.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(14) = Sheet30.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(15) = Sheet32.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(16) = Sheet34.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(17) = Sheet36.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(18) = Sheet38.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(19) = Sheet40.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(20) = Sheet42.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(21) = Sheet44.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(22) = Sheet46.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(23) = Sheet48.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(24) = Sheet50.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(25) = Sheet52.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(26) = Sheet54.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(27) = Sheet56.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(28) = Sheet58.Name[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]sheetName(29) = Sheet60.Name[/FONT][/COLOR]

Sheet 2 could be named as 201711001
sheet 4 could be named as 201711002
sheet 6 could be named as 201711003
and so on.
So, sheet2 (201711001) would be row 2
sheet4 (201711002) would be row 3
sheet6 (201711003) would be row 4
etc, etc

Each user determined sheet name (201711001) is referenced, named, and hyperlinked in column D of this particular worksheet, if that helps.
The 2017 and 11 come from the user's input at workbook creation and will change depending on what year and month the workbook represents.

As to the target range, the conditional formatting formulas/rules reference changes to columns I and K, so the target range could be I2:I31 and K2:k31 or I2:K31??

This conditional formatting macro is to run only on Sheet1.

Is that info helpful?

 

[mumps]

Re: Conditional formatting (interior cell color) with VBA help

I'm having a little problem visualizing what your data looks like. Could you post a screen shot of your sheet? Click this link for ways to do that(Section B): https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[Jmoz092]

Re: Conditional formatting (interior cell color) with VBA help

[TABLE="width: 1184"]
<tbody>[TR]
[TD]Date[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Tab Number[/TD]
[TD]Primary Payment[/TD]
[TD]Is Secondary Payable?[/TD]
[TD]Secondary Payment[/TD]
[TD]Total Ins Reimbusement[/TD]
[TD]Is Pt Responsible?[/TD]
[TD]Pt Portion[/TD]
[TD]All Ins Payments Received?[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD="align: right"]8/3/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708001[/TD]
[TD]Pending[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/3/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708002[/TD]
[TD="align: right"]$4,963.12[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD="align: right"]$4,963.12[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/3/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708003[/TD]
[TD="align: right"]$6,474.84[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD="align: right"]$6,474.84[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/3/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708004[/TD]
[TD="align: right"]$6,474.84[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD="align: right"]$6,474.84[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/3/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708005[/TD]
[TD]Pending[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/10/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708006[/TD]
[TD]Pending[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/10/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708007[/TD]
[TD="align: right"]$12,354.98[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD="align: right"]$12,354.98[/TD]
[TD]Yes[/TD]
[TD="align: right"]$40.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/10/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708008[/TD]
[TD="align: right"]$10,929.41[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD="align: right"]$10,929.41[/TD]
[TD]Yes[/TD]
[TD="align: right"]$2,426.86[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/17/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708009[/TD]
[TD]Pending[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/17/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708010[/TD]
[TD="align: right"]$6,495.59[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD="align: right"]$6,495.59[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/17/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708011[/TD]
[TD]Pending[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/17/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708012[/TD]
[TD="align: right"]$1,498.14[/TD]
[TD]Yes[/TD]
[TD="align: right"]$382.18[/TD]
[TD="align: right"]$1,880.32[/TD]
[TD]No[/TD]
[TD]None[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/21/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708013[/TD]
[TD]Pending[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/24/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708014[/TD]
[TD]Pending[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/24/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708015[/TD]
[TD="align: right"]$13,193.10[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD="align: right"]$13,193.10[/TD]
[TD]Yes[/TD]
[TD="align: right"]$163.17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/24/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708016[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/24/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708017[/TD]
[TD="align: right"]$10,685.01[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD="align: right"]$10,685.01[/TD]
[TD]Yes[/TD]
[TD="align: right"]$2,671.26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/24/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708018[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/24/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708019[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/31/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708020[/TD]
[TD]Pending[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/31/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708021[/TD]
[TD]Pending[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/31/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708022[/TD]
[TD]Pending[/TD]
[TD]Yes[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/31/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708023[/TD]
[TD]Pending[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/31/17[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD="align: right"]201708024[/TD]
[TD]Pending[/TD]
[TD]No[/TD]
[TD]Not Applicable[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]None[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/0/00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]201708025[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/0/00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]201708026[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/0/00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]201708027[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/0/00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]201708028[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/0/00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]201708029[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/0/00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]201708030[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jmoz092]

Re: Conditional formatting (interior cell color) with VBA help

The first and last names that have yellow or red text represent that that entire row is yellow or red, per the conditional formatting rules.

Rows that contain a first and last name represent that the entire row is green, per conditional formatting rules.

For rows without a first and last name, they have no interior cell color rule.

FWIW, columns F, K, and I have an interior color of (252, 233, 218). That, throughout most of our office's excel projects, signals that there is intended user input to that cell. Other than that, interior cell color is white.

 

[mumps]

Re: Conditional formatting (interior cell color) with VBA help

Thank you for that. Let's assume that a user will make changes in columns I and K. For example: A user enters "Yes" in I8 and K8="n/a" and the reference sheet would be in D8 which equals 201708007. So the code would look at range E49:E68 in that sheet. Is this correct? According to the code you posted, the following should happen:

 thecell.Interior.Color = RGB(252, 13, 27)

What range or cell would "thecell" refer to in this case?

 

[Jmoz092]

Re: Conditional formatting (interior cell color) with VBA help

What range or cell would "thecell" refer to in this case?

Yes, that is correct.

"the cell" would refer to Sheet1.Range("A8:K8")