Using If then statement to change cell fill color (sort of)
ฝัง
- เผยแพร่เมื่อ 23 พ.ย. 2024
- I know this is a bit of a misleading title, but this is basically what I searched when I was trying to find this solution...but you will be effectively using conditional formatting much in the same way that if/then statements are formatted
![[#2024MAMA] G-DRAGON - 무제(Untitled, 2014)+POWER+HOME SWEET HOME+뱅뱅뱅+FANTASTIC BABY | Mnet 241123 방송](http://i.ytimg.com/vi/Ox29z5Nf1Uk/mqdefault.jpg)
![[#2024MAMA] BIGBANG (빅뱅) - 뱅뱅뱅 (BANG BANG BANG) + FANTASTIC BABY | Mnet 241123 방송](http://i.ytimg.com/vi/EQsYfiPR9uM/mqdefault.jpg)
Thank you very much. Your's is the 4th video a watched on this topic and the only one who was able to help me.
great glad you found what you were looking for
Very Helpful. Been trying to work that out for a few hours!
great, glad it helped you out!
We can also put the color on the corresponding "Attending Party" cell.. isn't it? maybe saving a column for large worsheet..
How?
not sure i follow
Like everyone on here has already said....EXACTLY what I was looking for. Thank you so much
how did he cascade downed? I am unable to use it for the whole column can u help
ctrl+d cascades down
thanks a lot sir,
even i checked in my many Indian languages.. n many ways but finally i got it from you.. thanks a lot.. n subscribed with respect 🙏🙏🙏
glad it helped thanks for the sub
EXACTLY what I was looking for, THANK YOU THANK YOU, Brian!
glad it helped
Thank you ... I'm searching this for long time.🤓
great, glad you found it
EXACTLY what I was looking for. Thanks buddy
That's great! Happy it worked for you!
This is what I was looking for. Thank you very much!!
glad it helped
congrats,YOU ARE PROGRAMMING!!
i guess in a way
you are the angle in my nightmare. you saved me!
Glad it worked out. Please subscribe, tell you friends, and it you can think of another video idea let me know!
Thank you very much Mr.Brian. I was interested to learn this.
Glad it worked for you
Thank You, That is what i was looking for.
💕
great glad it helped
thank you, i can use it in our examination result preparations for our school.
oh cool.... glad it's working for ya!
This is just what i was looking for. thanks for sharing
Awesome. Glad it worked out for you. Please let me know if you have any other Excel questions you think may make good tutorial videos and please subscribe!
Thank you!!! This was exactly the video I needed to help me make mystery pixel art worksheets for my students. All the other videos I could find for the pixel art was in Google Sheets. You are a lifesaver!
great ... glad it helped
THIS IS SO HELP FULL. I KEEP FORGETTING THIS TECHNIQUE BETTER SUBSCRIBE.
sounds like a good idea.. sub sub sub
Excellent, thanks for this video. I needed this.
Awesome glad it worked for you. Any other Excel challenges you're looking for a video for? And please consider subscribing!
Great video clear instruction and simple execution. Thank you
glad it helped
Thanks Brian! Exactly what I was looking for! Thumbs up!
glad it helped
Mate you are a legend thank you so much for this!
Glad it helped! if there are any other issues you're having with excel please let me know, i'm looking for new video ideas.
This video was beyond helpful! Just wondering if it's possible to have the whole row be colored with a condition? I have cell that says either Yes or No, and I want the whole row to be colored red for no and green for yes. I know how to do it for one row, but can't figure out how to do this command for multiple rows. I can get the yes and no cells to change color exactly like in this video, but the rest of the row doesn't respond. Do I really have to do this rule for every row?
can you highlight all the rows you want impacted and get it to work? just make sure you highlight the row you are using for your equation first and then drag over the remainder of the rows you want impacted. i think excel keys off the row you start with in terms of what you can reference in your criteria statement
you can use absolute reference in your formula
Great video but why do I have a problem if I perform the exact same method Vertical instead of Horizontal like you showed.
hmmm... are you getting an error or just not producing what it should
thanks so much! Finally found a video after trying for an hour!
Superb video it saved my bacon clearly shown absolutely brilliant
great glad it helped
Just the tutorial I was looking for thank you so much for this video:) But I have one simple question, exactly at 1min 57secs of your video you said "we want it to cascade down and to get rid of the lock which it defaults to" so my question is how do I do that because the formula is only working for the cell at the top example (B18) and ignores all the rest underneath?
the lock in talking about is when there is an absolute reference to a cell (i.e., when there are "$" ...ex: $A$4...) you want to make sure you have highlighted the whole column you want the conditional formatting to work on, but in the equation you want to make sure to get rid of the $'s... does that help?
@@BrianLeeDick thank you i will give this a try and get back to you
@@BrianLeeDick I've removed the the $ and it worked! you are a genius thanks again!
glad it worked!
@@BrianLeeDick Hello again, I thought you'd be the best person to come back to and ask this question. In the "Conditional Formatting" I've chosen "Format only cells that contain", next I chose "Specific Text", once I apply it, it will then highlight all the cells containing those specific text, is there a way to get it to highlight every "Row" containing those text? Thanks
I was looking for it thanks 🙏
Ywbcwj zu vwinzu.bsibqqbsubxuwb
great glad it helped
It's good lesson but how can those colors replacement the words or grade for example where we have the marks 10 we need green color but number 10 no display how can I do?
hmmm...not sure i follow. You want to treat a number differently than a number + text?
Perfect just what I was looking for but I am putting a letter in the cell and having it change colour for a spreadsheet who is or who is not at work.
Great! Yup, great use case
I consider myself an advanced / expert user on excel and was doing this correctly, but couldn’t work out why it wasn’t working. I didn’t realise the $ locks cells 😂 learn something new everyday
oh cool, glad you picked something up from it
OK, but you could just place the conditional formatting to the same cell as the response cell instead of creating a whole new column just for the color! That way the cell that says "Maybe" will become yellow itself, etc...
are you asking about just conditional formatting a bunch of blank cells awaiting whatever you type
exactly what i was looking for. thanks a lot Man!!
great glad it worked
Thank you sir! i was looking for this
great! glad it helped
Thank you sir for nice and clear video, I have a question pleas. I make a table that contains in culomn A doctors names and the headers in row1 is the month date. Inside the table all the cells has data validation with departments in hospital to make rota shift for the doctors. The list of the data valedation contains (main department, long shift, short shift, emergency, operation room, DAY OFF).
I want to make a conditional FORMATING THAT IF I CHOOSE "long shift" from the data validation automatically gives this doctor "DAY OFF" for the next day and color the cell of "DAY OFF" in red.
How can I do that.( For example if I choose in C5 "long shift" automatically mak D5 "DAY OFF" with red background in B5.) thank you.
should be able to use conditional formatting with "contains'
It would have been helpful if we could have done it through nested if
anywasy ur tutorial was very helpful
never say never, i just personally don't know how to do it
Do you know how to do this for a date range greater than the cell chosen. For example, If I want all cells greater than 11/8/2018 to show as green, could I use the same concept?
should be able to yes
Good video. Very informational. Thanks.
glad it helped
Not sure what I am doing wrong :/ When I enter the formula for my first criteria it turns all of the cells the same color regardless of the data in the cell?
Never mind :) I forgot to delete out the $ to get rid of the lock.
glad you figured it out!
Hi Brian, based on the value if I want to change cell colour in different sheet then how to do it???
Sorry on delay -- just saw this...it should work the same as you see in the video. However when you are in the formula box of the conditional formula you will just switch over to the tab you're referencing and click on the cell in question. This will reference the cell in your equation as NameOfTab with a ! after it, and then the cell itself. Other than that, it should be the same process.
exactly what i was looking for
great! glad it helped
This is really helpful. But I just wanna ask, what if you have two statements to satisfy to make it change the color? Such as in a table of numbers for example, you need to determine a number by its row and column. Thanks a lot for this.
hmm... probably with an and statement but don't know exactly how to make it work though
@@BrianLeeDick thanks for the reply. I've figured it out back then and yes I used "and".
Really good video. Could you tel me how this would work for numerical data - say you needed a different colour for a cell/row of cells based on the value for a given cell... 200-299 (one colour), 300-399 (another colour)..and so on?
you should use that same conditional formatting menu and pick, i think, between is an option right?
=And(A2>=300,A2
Please do you know how to formulate the following condition in excel; when you have two cells one with high and the other with low value and one of the cell is colored green. so you want to return a value of '1' if the lowest value among the two cell is green color and '0' is the highest value among the cell is green. is there a condition or formula for the statement in excel
If i'm understanding your question correctly...and we take a mock scenario where your values in question were in cells a1:a2....you could start in a cell next to your values and type: =IF(MAX($A$1:$A$2)=A1,"0",IF(MIN(A1:$A$2)=A1,"1","")) ... then you'd do your conditional formatting on these values...
Hi there, Nice video. Can you achieve this same effect by having it fill multiple fields in color?
not sure if i follow
Good information
However this only work in some of the cells.
hmmm....that's odd...not sure why you'd be seeing that behavior
This does NOT answer the question of how to use IF to change cell color. This is simple formatting based on one cell value but I need to evaluate multiple columns to set the color so basically a nested IF statement or equivalent.
that's why i made the (sort of) call out. so you need to format based on values in multiple cells? like you want a1 to turn green if b1=10 and c1=20 or d2 is blank? something like that?
Thanks. But how was the for number like 0-10
not sure i follow
Thank you so much. Already liked and subscribed.
great, thanks a lot!
Oh MAn , you re so awesome, thank you so much!!!
glad it helped!
how to change the text automatically based on the value in another cell..?
think I'd need to see an example to help
Hi, your video was useful. When I tried to do as you did, it's not working for me. =E2="FALSE" Anything wrong with formula?
First thing I'm thinking, so let me know if this isn't the case...are you highlighting the appropriate range of cells BEFORE you put in the formula? If you change up this order excel does some wonky resetting that screws things up.
How can apply this rule in multiple tables in same and different sheets? Thanks in advance
You could either create a version you like then duplicate it.. otherwise I think it may require some vba coding
awesome video but when i save it, and re open it colours dissapear
hmmmmm.... that hasn't happened to me, so i don't even think i can help trouble shoot... that's an odd one. i see it's been a month though.. sorry on delay, did you ever figure it out?
can we do vice versa, if the cell color is red then the text is MAy be or yes etc
unfortunately i don't know how to do that... pretty sure there is a vba solution to this but it's above my head
How do you generalize the rule to include/color other columns (eg: D,E,F,G ...)?
if i understand the question. you should be able to do that by highlighting the area you want affected before you click on conditional formatting icon
Thank you! $ was the only issue.
how do you get rid of the $?
Great! You either delete it manually or you can toggle through the options with F2 when your cursor is behind the cell reference
how can i list the cells which is marked with the specific colors in the same excel separately?
So you want a cell that has the cell locations of a given entry? Like it says "A3, A5, A7"...or do you want a count of cells listed? like 3 green, 4 blue, etc.?
Thank you for your response.Is there a way to get a cell's value based on its color? I have highlighted some cells in bright yellow. I would like to enter a formula that will look at a column of values and enter only those cells that are yellow. In other words, the column that will have the formula will contain only bright yellow values.I have to list this separetely in the same excel.
@@swethams7354 you may be able to do it off the color, but I'm assuming you highlighted the values yellow because they matched some criteria. It'd be way easier to create a formula off of those criteria. Otherwise you're looking at needing yo bring in vba, and I'm farrrr from knowing how to do that
Does this work if the "color" column has numbers in it? example: I want D3 which is red and says Amanda to populate F3 which has a $amount to also be red, and then I have yellow Martina's and Green Hollys in the D column also. I want whatever color is in the cell of the D column to change the color of the cell in the F column. I've been trying all morning and cant get this to work . HELPPP pretty please
whoa... I'm sorry i don't follow... based on what I think I understand, it sounds like the traditional conditional formatting interface may not work. this may require some VBA coding
How to do the opposite to this..like, if we put black colour in one cell, i need "yes" as answer. If the cell is not black "No" as answer..
Interesting.... Not sure.. think you might need vba for that
So useful - Thank you!
glad it helped ya!
When selecting the box how do u casket or take the lock off on the second Page help please
casket?
I wanted to have the cell change colour every day. Fir example today blue, tomorrow green and next day different colour and so. Without having a date put in any column. Is this doable?
i think you'd need to have a date somewhere in your sheet so you could reference it in the conditional formatting... if you want to have it changing all the time... you'd need =today() somewhere in your sheet
Thank you very much ,that was so helpful
great glad it helped
Thanks for sharing, God bless....
glad to help
thank you very much. You just short my day :d.
glad it helped
hi, i noted a few weights down in kg and want to highlight another cell called ''current'' in red or green, if the value i put in there matches or is higher or lower than the weights (sort of counting the accomplishments). do you know how to do this?
It would probably require me seeing your Excel and making a new video
Thankyou very much this was really helpful
great glad it helped
hai
you are teaching very useful video sir
thanking you sir
you are more downloading sent TH-cam
glad it helped
what if I need the cell to change color upon any input I right in the other cell not just "Yes" or "No" I mean if I wrote a date or a text or any info, IS it possible?
should be.. in the formula section you would just have doesn't equal blank... which may be a default setting in more current excel. or you could write "" ... which means doesn't equal blank. and then for the result you pick which colors you want in the formatting section of the conditional formatting menu
Great explanation, thank you
Glad it worked for you
Thank you for this. What keys are you pressing to remove the $ when selecting cells?
F4
@@BrianLeeDick Thank you very much!!! Super helfpul.
can you combine the colour and attending party together. instead of three column, just 2, and showing yes with green together? thanks for the video
i just figure it out. no need to reply. thank you
Ga. A vavjaviq HVa. A. Vs ahvabqkqbowkqioqja
oh great!
Thanks, you're an angel!
glad this helped you
one doubt i tried giving if column value >600 then format to green but its not happening for me
What is happening? Nothing?
@@BrianLeeDick now its working, i didnot lock the formula (didnot removed $ symbol) while giving condition ,now its working fine ,Thank you
Thank Bro it's very helpful video
Glad it worked for you. Do you happen to have any similar questions/issues that you think would make good tutorial videos?
How did you get rid of the lock to make it cascade down?
F4 does it on most computers... My newest one it does something different. You can also click on the cell and just delete the $
How can apply this to different colossus- for example: if column A, B, C = text, then color another column (cell) ??????????
colossus?
what if the highlighted parts are needed to customized color?
it still the same, or different rules?
not sure if i follow
Hi, I'm having trouble finding it on google: what's the rule for this but the trigger being another cell being blank vs unblank. I'm doing room checks and have a comments cell for each room where I list any errors. I want the room number cell to turn red if there's any errors written down, so I guess =d1="literallyanytextaha,what'sthelineforthat" thanks!
Haha, from what I'm seeing I think it should work
Is it possible in google sheets with their to-do list, when the task is completed the header color changes to easily identify completed tasks? Thanks.
Not sure if you can do it on the to do list but you can still do conditional formatting in sheets nearly the same as with Excel... So could you do it that way? th-cam.com/video/yLzDf2af_Mg/w-d-xo.html here's a video where I show the process. Please consider subscribing.
This helped. Thank you.
great! glad it worked
How if E column input number color is red then A column input number and B column input text color also will be red ?
sorry -- don't think i follow the question
Sir before and after yes which sing you put ?
" "
Is there a way to change the cell colour whenever any data is entered in a cell regardless of what you enter?
You mean just "not blank"? You can make the equation "" ... That's left carat, right carat, quotes, quotes... That basically says doesn't equal blank
Informative, thank you
you're welcome glad it helped
This was perfect. Thanks.
great glad it helped
this was the first conditional formatting video that really illustrated how to apply multiple (and separate) conditional formats over the same range. In your example you needed three different formats over the same range and did three different conditional formats. Is it possible to add a single conditional format that covers all three conditional formattings?
Not that I'm aware of but that doesn't mean much I've never needed to do that so I haven't dug into it
Hello, is there a way to do this in only one step instead of 3??
not that I'm aware of unfortunately. definitely doesn't mean there isn't a way though
@@BrianLeeDick Thank you
Thank you , very useful
great glad it helped
i have not gettting this option in new formula how to do fix it give me solucation
do you mean you can't pull up the conditional formatting menu? or once there you don't have same options?
Thnx, lot u r great, I have desire to know if possible u teach us how v can scanning barcode with color by double scanning ,thnx
Sorry can you go into a little more detail? Are you trying to make a barcode in Excel?
It doesn't color my cells and just leave them as they are. I am following your examples to the letter. What am I doing wrong?
Hmmmmm... Hard to tell without watching what you're doing. Sorry
Can v apply change in colour in the same coloumn? If so can u elobrate it
not sure i follow
hy dear i need ur suggestion, i want that when i fill series it become colored.
e.g name in list become color when i put data in front of those names
not sure i follow exactly, but if you just want the new data you type to cause another to change color, you could just do "".... or does not equal nothing as your logic to change the color
HI , need to connect with you for same issue but bit different, can we ?
You can email me at condensedmba@gmail.com
Thank you so much! :)
no problem
if we have more than one word ?
it should still work as long as you put the phrase in quotes.... is it not working for you?
Thanks a lot Buddy 👍
👍
HEY! does anyone know how can i apply the same formula into the rows below??? i cant!!!
if i understand your question you have to make sure you highlight all rows you want formatted before you open the conditional formatting panel
Thank you!
np