Conditional Formatting in Excel to Create Fill Background

I have an Excel spreadsheet with Conditional Formatting.  (Attached). When any value appears in H20 and J20, Cells B20 through X20 are to have a fill background. It is currently formatted to do that but I do not believe it is correctly formatted (If you modify anything, suddenly the fill background drops out on P20 through X20! And, I want to do the same thing on each line, lines 4 through 28 and lines 34 through 58 and be able to create sheet after sheet below the current sheet with the conditional formatting following.
JTL-Log-17126-17150-EE4.xlsm
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
 
Thymos68Director of OperationsCommented:
That would be written like this:  =AND($H4>0,$J4>0)
  (the result is TRUE only when H and J are greater than zero. )

As an aside, you have an odd way of formatting the dates.  (forcing you to enter January 1st as "1012018", instead of simply 1/1, or 1/1/18 or 1/1/2018)
I would suggest you enter the dates as actual dates and let Excel handle them as dates.  That way they can be sortable, or easily reformatted.  You can still format them to display the way you currently have them by changing the custom number formatting to MM-DD-YY.  (the only drawback is that you would have to re-enter all of your current dates, so Excel can recognize them for what they are.)
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
Please note: When I do a Copy and Paste Special specifying only Conditional Formatting, it copies the cell values as well. When I only copy specifying Formatting, it copies the fill, but not the conditional formatting commands. This is why I do not think the Conditional Formatting is correctly set to begin with.
0
 
Rob HensonFinance AnalystCommented:
You only have the CF set on row 20.

Change the formula to:
=$H4>0

And then change the "applies to" range to:
=$B$4:$X$28

For the second set, formula should be:
=$H34>0
And applies to:
=$B$34:$X$58

For subsequent sets, so long as the formula refers to the first row of the "applies to" range then it should work.
1
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Rob HensonFinance AnalystCommented:
BTW there is an actual tick mark in WingDings font rather than using the square root symbol.

Format the cells as Wingdings and then press Alt and type 0252 on the numberpad, not the numbers across top of the keyboard.
0
 
Thymos68Director of OperationsCommented:
Just a note, I find it much cleaner to copy and paste formulas when I'm working with conditional formatting.  Otherwise, you'll start chopping up the conditional formatting ranges everytime you copy and paste, which stresses out Excel.
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
Thanks Rob and Thymos68,

One thing, I need the condition based on values being BOTH in H4 and J4. How would I phrase that?
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
That fixed it!  Thanks.  As for the date thing, we several software programs using the MMDDYY without dashes or slashes approach. With the volume of entry in those programs, the average user would enter a - or / more than 10,000 times per year. Therefore, we made Excel to work the same way for consistency. Thanks again.
0
 
Rob HensonFinance AnalystCommented:
How about sharing and giving some assistance points?
0
 
Thymos68Director of OperationsCommented:
Certainly a group effort
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.