Consulting

Results 1 to 19 of 19

Thread: conditional formatting to be macro on specific rows and colums

  1. #1
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location

    conditional formatting to be macro on specific rows and colums

    Hello, everyone.
    I would like to ask for your help because with conditional formatting I am doing it but it is very difficult to do it for 40 worksheets.
    The idea I will attach in a table is:
    In a given cell I have some value (number), at the end of the table I have some constant numbers.
    I'm trying a macro where I can tell exactly which worksheets (Array (peaches, bananas, paris, etc.) and in exactly specified cells to compare and if it's: greater than or equal to color the cells.
    just for Example:
    In cell A75, for example, I have 1,500.
    Values are 100, 200, 300, 400, 500, 600, etc.
    1500+100
    1500+200
    1500+300 ect.
    The check should be done in the cells described by me on this same row A75, for example in cells C75, G75, M75, Q75, etc.
    I will also attach a table.
    Thank you in advance for your assistance.
    row color cells.xlsm

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,183
    Location
    Excel already has this feature. are you sure you wish to re-invent the wheel? Have a look here. https://answers.microsoft.com/en-us/...6-64660f0a5b2f
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location
    Hello, to be honest, I have hot water, but I don't want to bathe under a tap, but with a shower. As I wrote above, I know how to do it with conditional formatting. I mark exactly the cells I want, then I say from which cell the number should be followed and if 1500+100 and if it finds a value greater than or equal to color it in the color I want. Then 1500+200 in another color and so on until it reaches the last value I set. Thank you very much for the link, but it doesn't say anything about macros, actually this about coyote, I'm asking for help. Because I have to make over 29 worksheets and with a macro it would be many times faster. Thanks again! Be alive and healthy!

  4. #4
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    If you don't mind me asking, why do you use two accounts?

    You have started the thread with one and replied with another, this could get confusing.

  5. #5
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,183
    Location
    The link shows you how to create a conditional format. All you have to do is record a macro using those steps, then bring it back here if it doesn't work correctly and we can try to adjust it where necessary.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    In your attached workbook, in Sheet1:
    Put the macro below into a standard code-module.
    Make sure Sheet1 is the active sheet
    Run the macro.
    Sub blah()
    totRow = 10
    Base = Range("A" & totRow).Value
    Set HighlightCells = Intersect(Rows(totRow), Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,W:W,Z:Z,AC:AC,AF:AF,AI:AI"))
    Set adds = Intersect(Rows(totRow), Range("AJ:AQ"))
    For Each cll In HighlightCells.Cells
      For i = adds.Count To 1 Step -1
        Set v = adds.Cells(i)
        If cll.Value >= Base + v.Value Then
          v.Offset(1).Copy
          cll.PasteSpecial Paste:=xlPasteFormats
          Exit For
        End If
      Next i
    Next cll
    Application.CutCopyMode = False
    End Sub
    Lots of hard coding, lots of assumptions. It's a start. Let's see if it's doing the right thing first.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location
    Hello, to be honest, it turned out that a long time ago, something happened and with the first account I could not log in, and then I made a second one. Since I am writing from two different places, the password of one was saved in one, and the other account in the other place. As for your question Why, it's because I first asked if the already perfectly working macro could Just be added, instead of running it separately in each sheet, with the push to do it in all the ones I specified. After which, as a P.S., I asked if it could, with some minimal change, do all this, but in certain selected cells, and not on the entire row. However, after that I thought that it's not right and I'd better ask it as a separate question, because the macro implementation is different, Yes it has a fairly close meaning, but still different. I didn't do it with any bad intention, on the contrary, I even think it's a bit upgraded. This one here now I will test it with thanks to p45cal and write. I honestly adore your site and I have to tell you that I have found a lot of things on the search engine that I have modified to my needs and it has been great, even with plugins after that. Thank you and stay healthy. As for what Aussiebear wrote. Yes you are right, but in the example this 1500+100,200,300,400,500,600,700, imagine how many times I had to click, set different colors until I got to the final, and then in all the other sheets, do it again and again and again

  8. #8
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location
    Hi p45cal,
    i tried the macro and everything is perfect.
    What you did to take the color below the value just amazed me.
    It's awesome, I'm speechless.
    Thank you from the bottom of my heart for this help.

    Only one thing left, how to make this macro action happen in Array (Peaches, London, Paris, New York, Something Else, etc.)
    Again, thank you in advance and I remain available!

  9. #9
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,183
    Location
    Maybe this might assist
    Sub blah()
        totRow = 10
        Dim sht as Worksheet
        Dim shtAry as Variant
        Set shtAry = Array("Peaches", "London", "Paris", "NewYork")
        For each sht in shtAry
            Base = Range("A" & totRow).Value
            Set HighlightCells = Intersect(Rows(totRow), Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,W:W,Z:Z,AC:AC,AF:AF,AI:AI"))
            Set adds = Intersect(Rows(totRow), Range("AJ:AQ"))
            For Each cll In HighlightCells.Cells
                For i = adds.Count To 1 Step -1
                    Set v = adds.Cells(i)
                    If cll.Value >= Base + v.Value Then
                       v.Offset(1).Copy
                       cll.PasteSpecial Paste:=xlPasteFormat
                       Exit For
                    End If
                Next i
            Next cll
        Next Sht
        Application.CutCopyMode = False
    End Sub
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    Quote Originally Posted by Aussiebear View Post
    Maybe this might assist
    Needs one more line (although a few more changes would avoid activating anything at all):
    Sheets(sht).activate after the For each sht in shtAry line.

    However, I said "Lots of hard coding, lots of assumptions."
    Hardcoding:
    The row to work on; is it always going to be row 10? Will that row always have the 100,200,300,400 cells in it? will there always be 8 such cells? Will they always be in columns AJ to AQ? Will the values always be 100,200 300 etc.? will the formats always be in the cells immediately below them? Will the formats always be the same formats?
    Will the cells to be highlighted always be in columns B,E,H,K,N,Q,T,W,Z,AC,AF,AI ?
    Sheets to work on: "more than 29". That'll make for a long hard-coded list in the code! Will it always be the same sheets? Will they all exist every time? Might the user change a sheet's name? Would it be easier to define which sheets are acted upon by their position in the workbook (you could act on all sheets between 2 other sheets)?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location
    Hello P45cal, I'll clarify right away. I have 32 sheets. Absolutely all are the same, with the same rows and columns, but each sheet has its own name of a given city. That's why I asked if you could help me to perform the same action in all of them, and not open each one separately and press a button to do the same thing for each sheet. As for the 10th row, I will change it according to my needs, it can be anyone. As for 100,200,300, etc., I will also adjust it according to my needs in the macro itself from where, how far to get info and color, even if up to 2000, I will just increase the range in the macro. But Yes, in all sheets, we are talking about an absolute copy of the same table and rows and columns. It is because of this that there is no need for very complex code. The names of the sheets will not change because only I fill them in and they are under my control. Even if there is a change, an addition, I will do it, so there is no danger of anything going wrong.As I already wrote about one sheet, it works great. My only request was how to write the names of all the sheets I want, so that with one click the same thing can be done in all of them. In your comment you say that one more line should be added besides the one for which he wrote the Aussiebear. I.e. is to add your line in the macro, is that how I understand? Thank you very much in advance!

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    I didn't test the code before saying what I said so here is a tested version of Aussiebear's + tweak:
    Sub blah()
    totRow = 10
    Dim sht
    Dim shtAry
    shtAry = Array("Peaches", "London", "Paris", "New York", "Something Else")
    For Each sht In shtAry
      Sheets(sht).Activate
      Base = Range("A" & totRow).Value
      Set HighlightCells = Intersect(Rows(totRow), Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,W:W,Z:Z,AC:AC,AF:AF,AI:AI"))
      Set adds = Intersect(Rows(totRow), Range("AJ:AQ"))
      For Each cll In HighlightCells.Cells
        For i = adds.Count To 1 Step -1
          Set v = adds.Cells(i)
          If cll.Value >= Base + v.Value Then
            v.Offset(1).Copy
            cll.PasteSpecial Paste:=xlPasteFormats
            Exit For
          End If
        Next i
      Next cll
    Next sht
    Application.CutCopyMode = False
    End Sub
    and a version which doesn't need anything to be activated/selected:
    Sub blah()
    totRow = 10
    Set shtAry = Sheets(Array("Peaches", "London", "Paris", "New York", "Something Else"))
    For Each sht In shtAry
      With sht
        Base = .Range("A" & totRow).Value
        Set HighlightCells = Intersect(.Rows(totRow), .Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,W:W,Z:Z,AC:AC,AF:AF,AI:AI"))
        Set adds = Intersect(.Rows(totRow), .Range("AJ:AQ"))
        For Each cll In HighlightCells.Cells
          For i = adds.Count To 1 Step -1
            Set v = adds.Cells(i)
            If cll.Value >= Base + v.Value Then
              v.Offset(1).Copy
              cll.PasteSpecial Paste:=xlPasteFormats
              Exit For
            End If
          Next i
        Next cll
      End With
    Next sht
    Application.CutCopyMode = False
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location
    Hello, everyone,
    p45cal, Aussiebear, georgiboy, you guys are amazing.
    There are moments when, out of happiness, one does not know what to say. In this case, it is exactly so.
    Once again, I am convinced that there are sites and people on them who, with their knowledge, can help and make someone else happy.
    Thank you from the bottom of my heart.
    If I had to give a reputation from 1-10, I would give a Million.

    P.S. - p45cal - if I can ask one last thing.
    When the macro does the coloring, those selected cells change a bit for me, almost below the default value.
    I mean where there is a value, whatever it is, for example 1782, after coloring (there is formatting in the cells, because they calculate data), then it shows me 1782.25874 - this is just an example.
    How do I get this integer back?
    I made this macro, but if there is some trick to get the formatting into yours, that would be great.

    Sub FORMAT0()
    
           Application.ScreenUpdating = False
        Sheets(Array("BLAGOEVGRAD TOTAL", "BURGAS TOTAL", "VARNA TOTAL", _
            "VELIKO TYRNOVO TOTAL", "VIDIN TOTAL", "VRACA TOTAL", "GABROVO TOTAL", _
            "DOBRICH TOTAL", "KYRDJALI TOTAL", "KUSTENDIL TOTAL", "PAZARDJIK TOTAL", _
            "PERNIK TOTAL", "PLEVEN TOTAL", "PLOVDIV TOTAL", "RUSE TOTAL", "SILISTRA TOTAL", _
            "SMOLQN TOTAL", "SOFIA TOTAL", "SOFIA OBLAST TOTAL", "STARA ZAGORA TOTAL", _
            "HASKOVO TOTAL", "SHUMEN TOTAL", "QMBOL TOTAL")).Select
        Sheets("BLAGOEVGRAD TOTAL").Activate
        
        Range("BO78,BR78,BU78,BX78,CA78,CD78,CG78,CJ78,CM78,CP78,CS78,CV78").Select
        Range("BX78").Activate
         'or Rows("78:78").Select
        Selection.NumberFormat = "0"
       
        Sheets("BLAGOEVGRAD TOTAL").Select
        Application.ScreenUpdating = True
    End Sub
    Last edited by k0st4din; 05-21-2024 at 12:57 AM.

  14. #14
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,183
    Location
    Hey if you are giving points, I'll have -1 and give the rest to the others.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  15. #15
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    There are 2 ways to go about this. The way it currently does this is to copy all the formats from the cells in columns AJ:AQ:
    v.Offset(1).Copy
    cll.PasteSpecial Paste:=xlPasteFormats
    So the first way is to ensure that all the formatting of those cells in columns AJ:AQ are as you want the target cells to be formatted (including number of decimal places).

    The second way is not to copy all the formatting but only to copy over certain aspects of the formatting: Here you can replace the 2 lines above with the likes of:
    cll.Interior.Color = v.Offset(1).Interior.Color 'copies the background colour of the cell
    cll.Font.Bold = v.Offset(1).Font.Bold 'copies the bold (or not) formatting.
    This will not affect the number formatting of the target cells; if you chose that to be zero decimal places, that'll be how it stays.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  16. #16
    snb
    Guest
    Sub M_snb()
      sn = Sheet1.Cells(10, 1).Resize(, 35)
      
      ReDim sp(7)
      For j = 0 To 7
        sp(j) = Sheet1.Cells(11, 36 + j).Interior.Color
      Next
      
      For j = 2 To UBound(sn, 2) Step 3
        If sn(1, j) > sn(1, 1) Then Sheet1.Cells(10, j).Interior.Color = sp((sn(1, j) - sn(1, 1)) \ 100 - 1)
      Next
    End Sub
    for all sheets:

    Sub M_snb()
      ReDim sp(7)
      For j = 0 To 7
        sp(j) = Sheet1.Cells(11, 36 + j).Interior.Color
      Next
      
      For Each it In Sheets
        sn = it.Cells(10, 1).Resize(, 35)
      
        For j = 2 To UBound(sn, 2) Step 3
           If sn(1, j) > sn(1, 1) Then it.Cells(10, j).Interior.Color = sp((sn(1, j) - sn(1, 1)) \ 100 - 1)
        Next
      Next
    End Sub
    Last edited by snb; 05-21-2024 at 07:06 AM.

  17. #17
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location
    Many thanks P45cal.
    I am thinking of replacing these 2 lines. Over time, I may forget that I need to format a cell in the range and still wonder why the value is changing.
    You're awesome, I'm just at a loss for words.....
    P.S - However, when I delete the first 2 lines and replace them with the second suggestion, it gives me an error and refuses to run the macro.
    Apparently I'm doing something wrong or I didn't understand it...

    v.Offset(1).Copy 'deleted this
    cll.PasteSpecial Paste:=xlPasteFormats 'deleted this
    cll.Interior.Color = v.Offset(1).Interior.Color 'I put this one
    cll.Font.Bold = v.Offset(1).Font.Bold 'and this one
    Last edited by k0st4din; 05-21-2024 at 09:51 PM.

  18. #18
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    In full:
    Sub blah()
    totRow = 10
    Set shtAry = Sheets(Array("Peaches", "London", "Paris", "New York", "Something Else"))
    For Each sht In shtAry
      With sht
        Base = .Range("A" & totRow).Value
        Set HighlightCells = Intersect(.Rows(totRow), .Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,W:W,Z:Z,AC:AC,AF:AF,AI:AI"))
        Set adds = Intersect(.Rows(totRow), .Range("AJ:AQ"))
        For Each cll In HighlightCells.Cells
          For i = adds.Count To 1 Step -1
            Set v = adds.Cells(i)
            If cll.Value >= Base + v.Value Then
              cll.Interior.Color = v.Offset(1).Interior.Color 'copies the background colour of the cell
              cll.Font.Bold = v.Offset(1).Font.Bold 'copies the bold (or not) formatting.
              Exit For
            End If
          Next i
        Next cll
      End With
    Next sht
    Application.CutCopyMode = False
    End Sub
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  19. #19
    VBAX Tutor
    Joined
    Sep 2012
    Location
    London
    Posts
    264
    Location
    Thank you very much!
    Everything is perfect and will save me a lot of formatting on everything that has been done so far.
    I repeat myself, but thank you very much!
    Stay alive and healthy and don't stop helping us who don't know!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •