• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting Multiple ‘IF Then’s With Specified Colors

I

indi visual

Member
Quite simply, I would like to sort and filter names and dates (in specified colors given the specified dates).


What has taken me four days to figure out will likely take someone in this forum seconds.

I am very new to all of this, and I come modestly, respectfully, and appreciative for any help.

I have a few questions, but they are all connected, and with a few short answers I can have my entire sheet functioning beautifully.


Conditional formatting is the most simple way to do this. However, I have a =ColorFunction sub that conditional formatting would interfere with (because it is my understanding the “=ColorFunction” does not recognize colors highlighted conditionally (or with private subs either). For this reason, I have thrown together an alternative sub macro that creates the same result that my colorfunction formula could recognize.


[pre]Sub Highlight_Date_Today_Red()<br />
'searches finds and highlights today date in range in a specified color without the use of standard conditional formatting<br />
' Highlight_Date_Today_Red Macro</p>
<p>Range("E4:E1000").Select<br />
Application.FindFormat.Clear<br />
'On Error Resume Next<br />
Cells.Find(What:=DateValue(Today), After:=ActiveCell, LookIn:=xlFormulas, _<br />
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _<br />
MatchCase:=False).Activate</p>
<p> With Selection.Interior<br />
.Pattern = xlSolid<br />
.PatternColorIndex = xlAutomatic<br />
.Color = 255<br />
.TintAndShade = 0<br />
.PatternTintAndShade = 0<br />
End With<br />
End Sub


It worked but it only highlighted one cell. Sad for me, and yes it is okay to laugh at my code (but at least give me credit for attempting this Frankenstein monstrosity) Lol


Here are my four questions:


How do I get it to search and highlight the current date today throughout the entire column accordingly?


As for multiple values, how would I get it to highlight yesterday’s date in another the color the same way accordingly?


Next, how would I write this same code with something along the lines of:


[pre]If Range(“H4”) contains “/”, then highlight cell (“E4”) in this color?[/pre]


Finally,


If Range(“H4”) contains no fill color, then highlight cell (“E4”) in this color?[/pre]


Any help on this would be super extremely appreciated you have no idea. I tried so many variations from so many forums for so long that it’s time I sought help from someone. Anyone who can help me this will have a speedy response from me guaranteed. I will be monitoring any responses round the clock. Thanks in advance.


Quick note: I did make sure the dates I attempted to find and highlight were formatted as “date cells” and not “text cells”.
 
Indi Visual


Firstly, Well done at having a go at using VBA to solve the problem

There is no need to worry about people laughing at your code here.

This forum is for people to ask and learn.

I think I can say on behalf of Chandoo that derogatory posts will be simply deleted.


I would do all of what your attempting with Conditional Formatting (CF)


First, select the area and remove any conditional formatting


Select yours rows and I have assumed your dates are in Column B but adjust the formulas if there not


For Today Date use

CF: =$B3=TODAY() and set a format


For Yesterdays Date use

=$B3=TODAY()-1 and set a format


Next select your Cells in Column E

add a CF you should see that CF already recognises that some cells will have formulas from your rows

Add a Formula


For contains a /

CF: =FIND("/",H16) and set a format


For contains a /

CF: =H3="" and set a format
 
Oh wow thanks Hui. All of these will work perfectly, except for my due today column (for all cells with the current date). You see, I have an =ColorFunction formula that counts the number of red cells in this column also (it counts the number of jobs I have due for that current day at work). However, if I use CF to format them in red, then the =ColorFunction will not read them as red (because it doesn't recognize conditionally formatted red), and my number will not calculate correctly.


Also, my "/" are from dates like "10/15". Is there anything I can add to the formulas you were nice enough to get me to search for that slash in the middle? I just basically need a formula that states if anything is in this cell at all (be it text or numbers or anything) then fill E4 green.


Any help on the ColorFuntion work around is my primary concern though.
 
You will need to use the

FormatConditions(1).Interior.Color property

not the cell color property


You may need to check a number of the CF's as some cells will have up to 4 CF's

ie


if FormatConditions(1).Interior.Color= xyz or FormatConditions(2).Interior.Color=xyz or FormatConditions(3).Interior.Color=xyz then do something


depending on which CF applies the color
 
Oh wow how easy was that, and to think I spent hours and hours trying to figure out what you solved in 2 seconds (in regards to the =H4<>""). As for the FormatConditions(1), could you elaborate on that a little bit more. I hate to feel like I'm wearing out my welcome, but you must understand I am truly on the verge to solving this entire worksheet with your help.


Is this FormatConditions code a sub, or is it a formula that works like the rest of the formulas you gave me.


My primary date column is E. I plan to use all of the formulas you gave me except for the current date which will be highlighted in red.


Are you saying that by changing the interior.color property that my ColorFunction counter can read it?


and if so, how exactly do I apply this type of formatting?
 
Indi visual


Which column do you want to count? and when do you want to count it ?

ie: I Think you want to count column E when it is Red, But when is it Red? ie: when Column H is ?
 
I would like to count column E4:E1000.


I would like to count the number of "today's date" in that column.


I have a cell in J1 with the following formula =ColorFunction($J$1,$E$4:$E$1000, FALSE)


Whatever color J1 is (which is red in this case),

it counts the number of cells in E4:E1000 that are just like it.


However, if those red dates are conditionally formatted in red, the J1 formula will read that there are '0' red current date highlight cells in the E Column even though there might be some (because it cannot read conditionally formatted red).


I've tried to conditionally format J1, so that all conditionally formatted red cells in the E column would match. But for one reason or another the only way this =ColorFunction formula works is if it's a regular fill of red (and not a CF of red).


Hence, why I am trying to frankenstein a way to change the interior color of all my dates today in the E column Without the use of conditional formatting.


I will say though however, you have been a tremendous help thus far, and I will be using CF on everything else.
 
Why not do a Countif

=Countif(E4:E1000,=today())


But I think you mean

=Countif(H4:H1000,=today())
 
Excellent Question.


I chose not to use a =Countif because I have a mark as complete button.


It marks that job which was due that day complete by filling that particular active row grey.


However, if I use a =Countif it will still count that job as needing to be finished.


I thought if I used the =ColorFunction, once marked grey the counter updates accordingly.


Any ideas on how to incorporate my mark as complete button to change my "jobs left" counter accordingly.


You're right, I really wish I could scrap this =ColorFunction idea all together, I'm just not sure how to update my counter once I've marked the job complete.


Any alternative suggestions?
 
One last question and I think I might be able to coast through this.

As for the formulas you provided me above.


Do I have to enter in each formula for all 1000 cells down?


I guess what I'm trying to ask is...


How would I write this formula if I wanted H4:H1000?


=$H4=TODAY()
 
Why not add a Status column and have your button update that

Then use a countifs function to count the number which are equal to today and complete etc


With regards the CF formula, select all the Rows first and then when you apply the formula just doit for the first row Excel will work out what to do for the others
 
I got it. Initially I didn't realize Excel would apply the additional formatting as needed. I see now how it works. Thanks a mill Hui.
 
You must log in or register to reply here.
Back
Top