If you have pasted a range of the format using the format painter, e.g., 10 cells, try to select those 10 cells and double click on the format painter and paste. This will allow you to format 10 cells at a time. It can be repeated using a larger range and so forth. Hope that is understandable.
You solution still works Chester! Once you have done it for 10 odd cells, just highlight them all, copy, and then paste special and only paste formatting. You can then rinse and repeat until you have 100 cells formatted, and then repeat again etc. Brilliant idea on the OFFSET function though, exactly what I needed.
that's great... I was using index function but was unable to copy formula to all cells. Double click format painter and paste it each row individually.... that's what I needed... Thanks!!!
Thanks, worked for my use case. When I copy and paste the result into powerpoint or outlook I loose the arrows unless I paste as picture (or embedded). Any trick to avoid having to paste as picture?
After doing doubleclick the format painter icon, you are selecting each cell to paste the format. instead you can use drop down key very simply continuously till end and it paste the format as expected. great video though. Appreciate the video.
How can you use the ''equal to'' preset conditional formatting in excel to format multiple values in a range at once and not one at a time? Or if there is a better alternative to this conditional formatting, what is it, please?
if i have a condition like that "Use icon sets again to display a green dot for each member of the loading crew (i.e. if the loading crew is set to 5, five dots should be green and one should be black). Also, complete the constraints box. For each constraint, display the word “Met” if the constraint is met and “Not Met” if the constraint is not met. Use the same icons from the model page to indicate whether the constraint is met. Also, turn the text red and bold if it says “Not Met.” "what i should do??
Really clever to add another column and doing (final - initial) to find a negative or positive result to obtain an increase or decrease. It seems like a pain to include another column, but since there is not another better solution (yet), this will do. Thanks for the idea.
I'm starter in learning Excel, When I use large values and do conditional formatting or Merge text, values get changed to #### this symbol. Is there any way to work on with.
Hi Chester, thanks for the video. I was searching a bit on web and found an idea which I extended a bit into the following macro... this should help to overcome the troubles you observed and automate the solution. I have reworked a different macro (I found on web) used for a different purpose but a logic works ok. Take care! Sub CondFormat() Dim rg As Range Dim iset As IconSetCondition Dim LastRow1 As Integer Dim i As Integer LastRow1 = Sheet2.Cells(Sheet2.Rows.Count, "I").End(xlUp).Row For i = 10 To LastRow1
Range("E4").Name = "ProCent" Sheet2.Cells(i, 4).Name = "Diff" & i
Set rg = Sheet2.Cells(i, 9) rg.FormatConditions.Delete Set iset = rg.FormatConditions.AddIconSetCondition With iset .IconSet = ActiveWorkbook.iconsets(xl3Symbols) .ReverseOrder = True .ShowIconOnly = False End With With iset.IconCriteria(2) .Type = xlConditionValueFormula .Operator = xlGreaterEqual .Value = "=(1+max(ProCent-0.2,0))*Diff" & i End With With iset.IconCriteria(3) .Type = xlConditionValueFormula .Operator = xlGreaterEqual .Value = "=(1+ProCent)*Diff" & i End With Next i End Sub
Nicely done, I need one help from you, can you please help me I have similar condition, not exact Please let me know so that I can send you my sheet Thanks, Yuvraj
I found a solution that worked for me without having to individually paste the formatting by using this formula: =INDIRECT(ADDRESS(ROW(),COLUMN()-1)) Hope this helps anyone.
I want to shuffle the number of 2 colums into 3rd but in different place for Example col1 = 1 2 3 4 5 col2 = 6 7 8 9 10 col3 = 5 8 1 9 2 10 6 4 3 7, Please reply
Хобби
![Megan Thee Stallion - Rattle [Official Audio]](http://i.ytimg.com/vi/qY6qwqVCsww/mqdefault.jpg)
If you have pasted a range of the format using the format painter, e.g., 10 cells, try to select those 10 cells and double click on the format painter and paste. This will allow you to format 10 cells at a time. It can be repeated using a larger range and so forth. Hope that is understandable.
Thank you very much Chester, I was struggling and so stressed but this helped me a lot
You solution still works Chester! Once you have done it for 10 odd cells, just highlight them all, copy, and then paste special and only paste formatting. You can then rinse and repeat until you have 100 cells formatted, and then repeat again etc.
Brilliant idea on the OFFSET function though, exactly what I needed.
Brilliant! Just what I was looking for - thanks.
brilliant! Love the workaround.
This actually works for me. Thank you for this kind of shortcut, it is very useful.
Thank you. Excellent walkaround!
that's great... I was using index function but was unable to copy formula to all cells. Double click format painter and paste it each row individually.... that's what I needed... Thanks!!!
Thank you, helps me a lot! :)
Worked Perfectly:)
very very helpful! thanks very much.
VERY VERY HELP FULL. THANK YOU
Thank you very smart sollution!
Thank you so much Chester 🙂
Nicely done, helpful.
Thanks a lot. This was helpful.
Great...been strugling with for sometime.
very helpful! thanks
Thank you very helpful
Thanks, worked for my use case. When I copy and paste the result into powerpoint or outlook I loose the arrows unless I paste as picture (or embedded). Any trick to avoid having to paste as picture?
nice thx, it helps me alot
Thank you so much
Ugh 2 hours later.... Thank you - Thank you so much!
After doing doubleclick the format painter icon, you are selecting each cell to paste the format. instead you can use drop down key very simply continuously till end and it paste the format as expected. great video though. Appreciate the video.
Also in the offset formula if you don't keep the reference cell as absolute, you can simply double the conditional formatting to apply on all cells.
How can you use the ''equal to'' preset conditional formatting in excel to format multiple values in a range at once and not one at a time? Or if there is a better alternative to this conditional formatting, what is it, please?
Brilliant!! I have been searching the exact same thing.!!
Glad it helped!
HI Chester, Did you find a better way since you uploaded the video?
Very useful, thank you.
Glad it was helpful!
if i have a condition like that "Use icon sets again to display a green dot for each member of the loading crew (i.e. if the loading crew is set to 5, five dots should be green and one should be black). Also, complete the constraints box. For each constraint, display the word “Met” if the constraint is met and “Not Met” if the constraint is not met. Use the same icons from the model page to indicate whether the constraint is met. Also, turn the text red and bold if it says “Not Met.” "what i should do??
Would this work for 3 or more columns?
For example: If I want to compare column A with B, B with C, C with D and so on? Appreciate your help.
Really clever to add another column and doing (final - initial) to find a negative or positive result to obtain an increase or decrease. It seems like a pain to include another column, but since there is not another better solution (yet), this will do. Thanks for the idea.
I'm starter in learning Excel, When I use large values and do conditional formatting or Merge text, values get changed to #### this symbol. Is there any way to work on with.
Hi Chester, thanks for the video. I was searching a bit on web and found an idea which I extended a bit into the following macro... this should help to overcome the troubles you observed and automate the solution. I have reworked a different macro (I found on web) used for a different purpose but a logic works ok.
Take care!
Sub CondFormat()
Dim rg As Range
Dim iset As IconSetCondition
Dim LastRow1 As Integer
Dim i As Integer
LastRow1 = Sheet2.Cells(Sheet2.Rows.Count, "I").End(xlUp).Row
For i = 10 To LastRow1
Range("E4").Name = "ProCent"
Sheet2.Cells(i, 4).Name = "Diff" & i
Set rg = Sheet2.Cells(i, 9)
rg.FormatConditions.Delete
Set iset = rg.FormatConditions.AddIconSetCondition
With iset
.IconSet = ActiveWorkbook.iconsets(xl3Symbols)
.ReverseOrder = True
.ShowIconOnly = False
End With
With iset.IconCriteria(2)
.Type = xlConditionValueFormula
.Operator = xlGreaterEqual
.Value = "=(1+max(ProCent-0.2,0))*Diff" & i
End With
With iset.IconCriteria(3)
.Type = xlConditionValueFormula
.Operator = xlGreaterEqual
.Value = "=(1+ProCent)*Diff" & i
End With
Next i
End Sub
Need one to compare dates. If one date is greater than 30 days etc or in between
Good
Nicely done,
I need one help from you, can you please help me
I have similar condition, not exact
Please let me know so that I can send you my sheet
Thanks,
Yuvraj
Quicker than what I was going to do which was manually format each cell
For 100 rows 😢
any solution for 365? because i try and it didnt' work.
I found a solution that worked for me without having to individually paste the formatting by using this formula:
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))
Hope this helps anyone.
I try but is not working, Can u teach me ?
I want to shuffle the number of 2 colums into 3rd but in different place
for Example
col1 = 1 2 3 4 5
col2 = 6 7 8 9 10
col3 = 5 8 1 9 2 10 6 4 3 7,
Please reply
+1 :)
thank you very helpful