Absolute legend -- thank you. We had teams organize status by highlight color. This allows me to start doing formulas because I have a unique number for each color. Thanks!
This was SUPER Helpful!!! Thanks! I was using conditional formatting as well and the ".Value = c.DisplayFormat.Interior.ColorIndex" advice worked PERFECT. Thanks for embedding it in the comments as it saved me tons of time! :) :)
You are very welcome! I am glad that it helped :)) I will pin a comment with that code tidbit in it now in case it helps anyone else in the future. If you need any additional help, feel free to ask in our forum as I am not always so quick to respond here lol.
Thank you. I am trying to do a scorecard and use this. If yellow highlight of text in E3 then 5 points, If yellow highlight of text in F3 then 10 points,. If yellow highlight of text in G3 then 15 points, and in Far right Column put the Score for each line of 5, 10 or 15.
What if the cell coloum that has colour you’d like to change every now and again (say green and red), how do you do go about having a formula that automatically sums up said cells only when it’s fills are green and not red? Thanx in advance 😊
Always excellent. Do you have any advice on updating code when inserting adding and deleting columns? Is there a way to make the cell / range references dynamic in the code? Thank you
You can use .Font.Color = -16776961 or something like that - to figure out the number, just record a macro and set the cell to the desired color and then view that macro.
is there a way to get a "True" or "False" value by comparing the colour of 2 different cells? For Example, if Cell A1 and B1 are both filled in with Red then cell C1 should say "Match" or "True". And to base it on conditional formatting colours?
Just make one formula that combines the two conditional formatting formulas and apply that to the third cell and put it in an IF statement function that outputs "True" or "Match" If you need more specific help, upload some samples to our forum and ask there and it will be easier to help: www.teachexcel.com/talk/microsoft-office?src=yt_comment
I changed it to formula type in case anyone prefers it and because i had trouble figuring it out: Function FillColor(Ref) Application.Volatile FillColor = Ref.Interior.Color End Function completely based on empirical testing, there might be stuff not needed in it , sorry visual basic bros, just copied from another and chenged it until it worked
Is there anyway to do this to a column of cells that have conditional formatting set to change the color? Essentially I have a column that is set with specific texts to change the colors accordingly. But when I try to run the macro it unfortunately gives me the same code for the entire column. Instead of coding the colors I think it's actually coding the formatting instead? Any thoughts would be greatly appreciated.
Hi there, go ahead and ask this in our forum: www.teachexcel.com/talk/microsoft-office It's much easier to give you help there and you can upload a sample file too.
I can't look at the video now (just this comment), so I don't know the exact code that you are talking about, but, you don't have to declare variables to use them if you don't use option explicit, not usually the best idea but ok for small things like this, and making "c" equal the cell references is standard for how the For Each loop works - you could rename "c" to "myCell" or "anyOtherName" so long as it is a valid variable name.
Nice to know about colour character also can consider for function based on requirement. I got above clip while googling about my requirement. But not able to fix it. Can you help me to fix please.
I have data in A2 to A10 as {9:15:18:25:32:35:40:48:50} and in B2 to B10 as{4:5:9:15:21:28:42:48:50} My partial requirement fulfilled with this formula in C2 to C10 "=if(ISNA(Match(A2,$B$2:$B$10,0)),A2," ")" Which gave me the result in C2 to C10 as {" ":" ":18:25:32:35:40:" ":" "} Now I coloured yellow for all cells of A2 to A10 and coloured B4 and B9 as yellow by using the colour functon I should get the result in C2 to C10 as {9:" ":18:25:32:35:40:48:" "}
Is there a way to get this to work where the cells have been filled with a color using conditional formatting. I tried this on a datasheet where the cells were filled with red using conditional formatting, but the macro did not produce a number. The destination cell remained black. All of the non-red cell (no fill) did return a value (16777215 ). Thank you.
i need help comparing two cells with a high and low value, one of the cells is blue. Return 1 if the lowest value is blue color or return zero if the higher value is blue color. how do i perform this task in excel?
Depends on a few factors, please post this question on our forum along with a sample file and we can better help you there: www.teachexcel.com/talk/microsoft-office?src=yt
This was great but did not return the result I was looking for. Cell colors that I want to identify have been created with conditional formatting (duplications give a red background). When the Macro runs it returns the result "16777215" for a blank cell. Its as if the Macro does not see the color. Any advice?
Conditional formatting works different. I'm not in front of Excel right now, but here is another color option: c.Interior.Color (original) c.DisplayFormat.Interior.ColorIndex (another option to try)
![ปฏิเสธไม่ไหว (Crush on you) - LIPTA feat. No One Else [TEASER MV]](http://i.ytimg.com/vi/VC689EgdSIw/mqdefault.jpg)
For Conditional Formatting Colors use: c.DisplayFormat.Interior.ColorIndex
Thanks but I didn't understand how to use this in conditional formatting. please give an example. Thanks you
@@saeedrafiee1444 I think I got this to work. The third line should read >>>
c.Offset(0, 1).Value = c.DisplayFormat.Interior.ColorIndex
Absolute legend -- thank you. We had teams organize status by highlight color. This allows me to start doing formulas because I have a unique number for each color. Thanks!
You are very welcome! I'm glad I could help! :)
This was SUPER Helpful!!! Thanks! I was using conditional formatting as well and the ".Value = c.DisplayFormat.Interior.ColorIndex" advice worked PERFECT. Thanks for embedding it in the comments as it saved me tons of time! :) :)
You are very welcome! I am glad that it helped :)) I will pin a comment with that code tidbit in it now in case it helps anyone else in the future. If you need any additional help, feel free to ask in our forum as I am not always so quick to respond here lol.
Thank you for teaching this! It has saved me a ton of time and headache.
Excellent as always, How to get HEX format plz. Thanks
Thank you. I am trying to do a scorecard and use this.
If yellow highlight of text in E3 then 5 points, If yellow highlight of text in F3 then 10 points,. If yellow highlight of text in G3 then 15 points, and in Far right Column put the Score for each line of 5, 10 or 15.
Super it resolves my problem correctly. Thanks
Select All
Sub ListColors()
For Each c In Selection
c.Offset(0, 1).Value = c.Interior.Color
Next c
End Sub
Very helpful. You tutorials are always very practical and well-explained. Thank you.
Thanks! Glad to help :)
What if the cell coloum that has colour you’d like to change every now and again (say green and red), how do you do go about having a formula that automatically sums up said cells only when it’s fills are green and not red? Thanx in advance 😊
Hi. Can you make a formula / when i change the color of cell , the cell next to it will show todays date. Thank you
Good Tutorial, Thanks
Always excellent. Do you have any advice on updating code when inserting adding and deleting columns? Is there a way to make the cell / range references dynamic in the code? Thank you
Thank you , this was really helpful, although I am wondering how can I apply if this if using google sheets instead of Excel , thanks
I need to look down for my color. as my colors are in columns so offset will not work. thanks for the idea
Hello, I love this, what would I have to change in the VBA to isolate text color instead of cell color? Thank you!
You can use .Font.Color = -16776961 or something like that - to figure out the number, just record a macro and set the cell to the desired color and then view that macro.
is there a way to get a "True" or "False" value by comparing the colour of 2 different cells? For Example, if Cell A1 and B1 are both filled in with Red then cell C1 should say "Match" or "True". And to base it on conditional formatting colours?
Just make one formula that combines the two conditional formatting formulas and apply that to the third cell and put it in an IF statement function that outputs "True" or "Match" If you need more specific help, upload some samples to our forum and ask there and it will be easier to help: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Will this work for cells that have a conditional formatting for color?
I changed it to formula type in case anyone prefers it and because i had trouble figuring it out:
Function FillColor(Ref)
Application.Volatile
FillColor = Ref.Interior.Color
End Function
completely based on empirical testing, there might be stuff not needed in it , sorry visual basic bros, just copied from another and chenged it until it worked
Is there anyway to do this to a column of cells that have conditional formatting set to change the color? Essentially I have a column that is set with specific texts to change the colors accordingly. But when I try to run the macro it unfortunately gives me the same code for the entire column. Instead of coding the colors I think it's actually coding the formatting instead? Any thoughts would be greatly appreciated.
Hi there, go ahead and ask this in our forum: www.teachexcel.com/talk/microsoft-office
It's much easier to give you help there and you can upload a sample file too.
same problem here. Did you manage to find a solution?
Very helpful, thanks
color numbers come back the same on some of the different colors - please help
I just was wondering how excel interpreted "for each c" or whatever it is as " for each cell" in the selection although we did not declare that ?!
I can't look at the video now (just this comment), so I don't know the exact code that you are talking about, but, you don't have to declare variables to use them if you don't use option explicit, not usually the best idea but ok for small things like this, and making "c" equal the cell references is standard for how the For Each loop works - you could rename "c" to "myCell" or "anyOtherName" so long as it is a valid variable name.
ممنون
Nice to know about colour character also can consider for function based on requirement. I got above clip while googling about my requirement. But not able to fix it.
Can you help me to fix please.
I have data in A2 to A10 as {9:15:18:25:32:35:40:48:50} and in B2 to B10 as{4:5:9:15:21:28:42:48:50}
My partial requirement fulfilled with this formula in C2 to C10 "=if(ISNA(Match(A2,$B$2:$B$10,0)),A2," ")" Which gave me the result in C2 to C10 as {" ":" ":18:25:32:35:40:" ":" "}
Now I coloured yellow for all cells of A2 to A10 and coloured B4 and B9 as yellow by using the colour functon I should get the result in C2 to C10 as {9:" ":18:25:32:35:40:48:" "}
Is there a way to get this to work where the cells have been filled with a color using conditional formatting. I tried this on a datasheet where the cells were filled with red using conditional formatting, but the macro did not produce a number. The destination cell remained black. All of the non-red cell (no fill) did return a value (16777215
). Thank you.
My fault. The destination cell was not blank. It contained 255; it was just white font for some reason.
no worries)
i need help comparing two cells with a high and low value, one of the cells is blue. Return 1 if the lowest value is blue color or return zero if the higher value is blue color. how do i perform this task in excel?
Depends on a few factors, please post this question on our forum along with a sample file and we can better help you there: www.teachexcel.com/talk/microsoft-office?src=yt
This was great but did not return the result I was looking for. Cell colors that I want to identify have been created with conditional formatting (duplications give a red background). When the Macro runs it returns the result "16777215" for a blank cell. Its as if the Macro does not see the color. Any advice?
Conditional formatting works different. I'm not in front of Excel right now, but here is another color option:
c.Interior.Color (original)
c.DisplayFormat.Interior.ColorIndex (another option to try)
@@TeachExcel -That worked and thank you! Exactly what I was looking for.
It worked! Fantastic! Thank you!
Can u pliz teach us shortcut keyboard key that will be using in excel..
What about this one? th-cam.com/video/Kx83RZV-9oI/w-d-xo.html
Thank you sir