Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA for Excel Spreadsheet using Conditional Formatting

    Hi there,

    Not sure if i'm in the correct forum but i need help on a VBA i'm using.

    I'm using the following VBA, when i fill a cell with colour RGB(0, 0, 0) then the cell is skipped over when i'm entering data. So far it only works if i manually fill in the colour into selected cells.

    Code:
    If ActiveCell.Interior.Color = RGB(0, 0, 0) Then
    ' Has Line, Select Next Cell
    ActiveCell.Offset(1, 0).Range("A1").Select
    End If
    
    End Sub
    What i want, is for it to work on conditional formatted cells.

    I have a conditional format "Use a formula to determine which cells to format", the formula i'm using is from a different spreadsheet as follows:
    ='Layout Details'!$U1="N"
    I have this formatted to fill cells RGB(0, 0, 0) and everything up until this point is working correctly.

    However, the VBA won't work on the conditionally formatted cells, when i'm entering data it doesn't automatically skip over the conditionally formatted cells, however if i manually fill the cell then it works.

    What do i need to change in the VBA so that it works on the conditionally formatted cells?

    Many thanks

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Conditionally formatting the interior color of the cell does not actually change the cell's "base" color. If the condition is not met the cell's color returns to the base color and that is what VBA will look for. You best bet is to trigger off of the condition that changes the cells color. Perhaps, something like this:
    Code:
        Windows("Layout Details").Activate
        If Range("U12") = "N" Then
             Windows("CurrentWoorkbook").Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
        End If
        Windows("CurrentWoorkbook").Activate
    HTH,
    Maud

  3. #3
    New Lounger
    Join Date
    Oct 2014
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Thanks for trying to help but that didn't do anything at all. Thanks anyways

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I had put the wrong reference to the desired cell. Try this code

    Code:
    Public Sub CheckCondition()
        Windows("Layout Details.xlsx").Activate
        If Range("U1") = "N" Then
             Windows(ThisWorkbook.Name).Activate
            ActiveCell.Offset(1, 0).Range("A1").Select
        End If
        Windows(ThisWorkbook.Name).Activate
    End Sub
    It will shift the cell selection from the active cell in the current workbook based on the value in Cell U1 from your Layout Details workbook. Replace Layout Details.xlsx with Layout Details.xlsm if it is macro enabled. The workbooks must both be opened in the same instance of Excel.

    HTH,
    Maud

  5. #5
    New Lounger
    Join Date
    Oct 2014
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again Maud, but unfortunately it just doesn't seem to want to work. Thanks for trying for me though, i really appreciate it.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    The code works fine but perhaps it is not doing what you expect. Can you post a sample and I will integrate it.

    From what I am understanding, you have the Value "N" in a cell (U1) on another workbook. Based on the value, you have a conditional formatting set to look at that cell in the other workbook and turn a cell black in the current workbook if "N" is present. Now, if the cell is black, you want the active cell to move to the one below it.

    What my code does it to look at the cell (U1) in the other workbook. If it is "N", the move the active cell in the current workbook to the one below it. You can still have your conditional format set to turn the cell color black but it will not be used in change the active cell.

    Maud

  7. #7
    New Lounger
    Join Date
    Oct 2014
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Thanks, if you are able to do it then that would be amazing! I've attached a copy of the file.

    I've hidden all the irrelevant tabs, so theres just the "Layout Design" tab and "1" tab.

    On the "Layout Design" tab you will see two columns with letters "Y" or "N" in them, this is what is referenced in the conditional format on tab "1". When the cell in "Layout Design" has the "N" showing, then the cells in "1" will format to fill RGB 0,0,0. This is where i need the VBA to work so that when i start entering data, the black cells are automatically skipped over as there won't be data for this particular cell ever.

    Thanks
    Attached Files Attached Files

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I am gathering that this thread is one part of your project and the other thread is a complementing piece.

    Using your spreadsheet, I have placed conditional formatting in B7 thru L102 that if the cell contains "N" then make its fill color black. The VBA code below cycles through each cell in that range the looks at the corresponding cells in col T or col U in the Layout Details sheets. If the value is an "N" then it also places an "N" in the cell back in the Active sheet. The conditional formatting will now turn it black and focus moves to the next cell. If the value is a "Y in the Layout Details sheet, the code will execute what ever code you place in the section indicated. I have placed a simple line that will input "Not N". The end result is that an "N" in the Layout Details sheet dictates if the conditional formatting will turn the cell black then skip to the next cell or let your inserted code manipulate the cell.

    HTH


    Code:
    Public Sub CheckConditionN()
    '------------------------------------
    'DECLARE AND SET VARIABLES
        Dim I As Integer, J As Integer, Col As Integer
    '------------------------------------
    'CYCLE THROUGH CELLS
        For I = 2 To 12
            If I = 7 Then GoTo Done
            For J = 7 To 102
    '------------------------------------
    'MATCH ACTIVESHEET AND LAYOUT DETAILS COLUMNS
                Select Case I
                    Case 2, 5, 8, 11
                        Col = 20
                    Case 3, 4, 6, 9, 10, 12
                        Col = 21
                End Select
    '------------------------------------
    'EVALUATE IF ACTIVESHEET CELL SHOULD HAVE AN "N"
    'FOR CONDITIONAL FORMATTING TO CHANGE CELL COLOR
                With Worksheets("Layout Details")
                
                If .Cells(J, Col) = "N" Then
                    Cells(J, I) = "N"
                Else:
                
                    'EXAMPLE CODE
                    Cells(J, I) = "Not 'N'"
                    'YOUR CODE GOES HERE INSTEAD
                
                
                End If
                End With
            Next J
    Done:
        Next I
    End Sub
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Oct 2014
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks mate but not sure if you know what your doing, that just did something incredibly bizarre i've never seen. Don't worry, i will just manually colour the cells then. Thanks for trying

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    LOL...that's a first. What the code did was to demonstrate a way that values could be written to every cell except those filtered by an "N" from another source. Pity you don't see that because that was exactly what you wanted. I think you just don't know how to apply it into your application.

  11. #11
    New Lounger
    Join Date
    Oct 2014
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually it is exactly not what i wanted at all. I enabled the Macro, no cell was filled in the black colour, so when i clicked on that button you put in there titled "Run Code ", every cell that wasn't conditionally formatted entered the words "Not N", i don't want that to appear, i want the cells to appear blank so i can enter the volumes into the cells.

    Even when i begun entering data in to replace the "Not N" entry, when i hit 'Enter' on my keyboard to skip the black cells, it didn't skip them at all, i was able to enter data and when i did the black disappeared and became white with the data i entered.

    This is definately not what i want, not at all.

  12. #12
    New Lounger
    Join Date
    Oct 2014
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also, when i edited some of the other levels and placed the letter "N" into the cells, the macro page didn't change at all, it remained with the white cell saying "Not N", even when i hit the Run Code button again

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    You will notice in the code the I wrote sample code ...place your code here instead. The code works perfectly if you were using code to place your data. Apparently, you are not entering your data by code..

    Your solution should be simple if you are manually entering your data. A worksheet change procedure or a worksheet selectionchange procedure to offset the cell can easily be written by using target.offset and by checking the conditional formatting property of the cell for true/false.

    If no one tackles this, then I will write the code when I get back.

    Maud
    Last edited by Maudibe; 2014-10-05 at 08:41.

  14. #14
    New Lounger
    Join Date
    Oct 2014
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok....thanks. I have no idea what any of that actually means. I don't know how to write VBA codes, don't even understand them. I only know how to view the code and paste whatever someone else gives me, how it's actually written and how it works i have no idea; so none of that made any sense to me what so ever. I was just hoping someone could give me the VBA code to copy and paste into my workbook and then it would just work for me, thats how i've done it up til now.

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Ok, check-in tomorrow. I should have something by then. Sorry for the confusion.

    Maud

Page 1 of 2 12 LastLast

Posting Permissions

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