Changing background at conditional formatting is malfunctioning

C

csbaros

New Member
Joined
Apr 22, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Dear All,
Pls help me to figure out the solution:

I would make and copy paste couple of hundred times 4x3 cell blocks. One block contains one condition in the drop-down menu. The background, in case of 12 cells have to depend on the condition which belongs to the certain block . In this example F1 means yellow.
If I not use $ in the rule (like in the example below), then it only change one cell background color, instead of changing all the 12.
If I use $ in the role, then at copy paste the rule cell stays the same for all blocks. Not gonna change dynamically for the new block actual rule cell.

issue3.jpg


Is there any idea to solve it ?

I hope, I've described it clearly. If not please find me and I try it again.

Thank you very much

Csaba
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Welcome to the Board!

Yes, this is a tricky one!

The only way I can think of it is to use VBA code to build these "blocks", so you can dynamically figure out the formula to use for each block.

Here is a quick little sampel I worked up. You simply select a 3x4 range to apply it to, and run the code. It will put in the borders and conditional formatting for your first condition (F1).
Note that I got a lot of this code by using the Macro Recorder.
VBA Code: 
Sub MyBlockMacro()

    Dim radd As String
    Dim frm As String

'   Confirm size is three rows by 4 columns
    If Selection.Rows.Count <> 3 Or Selection.Columns.Count <> 4 Then
        MsgBox "You MUST select a 3x4 range to start!", vbOKOnly, "ERROR!"
        Exit Sub
    End If
   
'   Put borders around block
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
       
'   Get address of first block in second row for CF rule
    radd = ActiveCell.Offset(1, 0).Address

'   Build first conditional formatting rule
    frm = "=" & radd & "=" & Chr(34) & "F1" & Chr(34)

'   Add first conditional formatting rule to first cell in second row of block
    Selection.FormatConditions.add Type:=xlExpression, Formula1:=frm
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub
 
Upvote 0
Joe4 said:
Welcome to the Board!

Yes, this is a tricky one!

The only way I can think of it is to use VBA code to build these "blocks", so you can dynamically figure out the formula to use for each block.

Here is a quick little sampel I worked up. You simply select a 3x4 range to apply it to, and run the code. It will put in the borders and conditional formatting for your first condition (F1).
Note that I got a lot of this code by using the Macro Recorder.
VBA Code: 
Sub MyBlockMacro()

    Dim radd As String
    Dim frm As String

'   Confirm size is three rows by 4 columns
    If Selection.Rows.Count <> 3 Or Selection.Columns.Count <> 4 Then
        MsgBox "You MUST select a 3x4 range to start!", vbOKOnly, "ERROR!"
        Exit Sub
    End If
  
'   Put borders around block
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
      
'   Get address of first block in second row for CF rule
    radd = ActiveCell.Offset(1, 0).Address

'   Build first conditional formatting rule
    frm = "=" & radd & "=" & Chr(34) & "F1" & Chr(34)

'   Add first conditional formatting rule to first cell in second row of block
    Selection.FormatConditions.add Type:=xlExpression, Formula1:=frm
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub
Click to expand...
Dear Joe,

Thanks a lot for your help. It is partly solved my issue. Since I'm not so professional these kind of issue, may I ask further help from you ?

For better understanding I attached the sample:
issue1.xls.xlsx

Please help me to switch between the colors for the whole block according the value of the dropdown menu (data validation).

Since I will have a couple of hundreds blocks, is it only possible to change block by block ? Not possible to automatize this ?

Thank you very much

Appreciate it

Csaba
 
Upvote 0
I am a bit confused. The sample you provided only color one single block of the 12.
My code formats all 12 of them. Isn't that what you want?

Are these blocks pre-existing, or are you building them on-the-fly?
If they are pre-existing, is there some "pattern" that the follow?
In your first post, it looks like they are scattered willy-nilly all over the place with no distinct pattern.
In your second post, it looks like the follow a certain pattern.

When asking questions like this, to get the best results, you should:
1. Provide exact details, i.e. including ranges where things exist etc.
2. Do NOT oversimplify things for the sake of posting them here. That can lead to situations where you get an answer to the precise question you asked, but doesn't really work for your actual situation. Unless you are very proficient at VBA and feel comfortable modifying code, you should post your problem, as it really exists for you, not some simplified version of it.
3. Post a sample of your current data structure, and post a sample of your expected results (exactly what you want it to look like).
 
Upvote 0
Joe4 said:
I am a bit confused. The sample you provided only color one single block of the 12.
My code formats all 12 of them. Isn't that what you want?

Are these blocks pre-existing, or are you building them on-the-fly?
If they are pre-existing, is there some "pattern" that the follow?
In your first post, it looks like they are scattered willy-nilly all over the place with no distinct pattern.
In your second post, it looks like the follow a certain pattern.

When asking questions like this, to get the best results, you should:
1. Provide exact details, i.e. including ranges where things exist etc.
2. Do NOT oversimplify things for the sake of posting them here. That can lead to situations where you get an answer to the precise question you asked, but doesn't really work for your actual situation. Unless you are very proficient at VBA and feel comfortable modifying code, you should post your problem, as it really exists for you, not some simplified version of it.
3. Post a sample of your current data structure, and post a sample of your expected results (exactly what you want it to look like).
Click to expand...
Hello Joe,

You absolutely right, I've oversimplified the issue, because it looks to complex (but maybe just for me). I didn't want to make it on a complicated way.
Would you be so kind and try to help me ? I try to explain it again and show the issue as it is.

I've attached the real sheet, with the real pattern.
Answering you question: I will create these blocks. These are copies of real blocks from a design drawing of a photovoltaic plant. The pattern will follow its design.
( It's still not the whole sheet, but it will be much more bigger).
I will have to different type of blocks (block boundary marked by thicker line):
- Full block (F): 3x27 cells
- Half block (H): 3x9 cells
Both of them has 4 different status, represented by 4 different colors.
Status could be chosen from the drop-down menu, in each block first column, second row.
Something like in the pictures below:

1619373516899.png
1619373589167.png


I've set the formatting rules for the first two blocks in the first row.

Now I've attached the real xls: Park1.xlsx

I would like to copy the same formatting all over the whole xls for all blocks. (I tried it with macro, but it wasn't successful.)

Later on I will make another xls from another drawings and I would like to use the same method.

I hope, that now I'm more specific. If you will help me further and something not clear, I'll try to clarify it. The solution for me is so important, I cannot see other method.

Thank you very much for your time

Csaba
 
Upvote 0
OK, if you already have the blocks on the sheet, and what to color them by entering one of those eight codes, I would probably use VBA instead of Conditional Formatting. We can use Event Procedure code which will run automatically whenever we enter one of those eight codes in a box.

In order for this code to run, it must be placed in the sheet module. You can get there by right-clicking on the sheet tab name at the bottom of the screen, selecting "View Code", and past this code in the VB Editor window that pops-up.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim clr As Long
    Dim blk As Long
    Dim rng As Range
    Dim r As Long
    Dim c As Long

    If Target.CountLarge > 1 Then Exit Sub

    Select Case Target
        Case "H1"
            clr = 65535
            blk = 8
        Case "H2"
            clr = 49407
            blk = 8
        Case "H3"
            clr = 255
            blk = 8
        Case "H4"
            clr = 12611584
            blk = 8
        Case "F1"
            clr = 65535
            blk = 26
        Case "F2"
            clr = 49407
            blk = 26
        Case "F3"
            clr = 255
            blk = 26
        Case "F4"
            clr = 12611584
            blk = 26
    End Select
    
    If blk > 0 Then
        r = Target.Row
        c = Target.Column
        Set rng = Range(Cells(r - 1, c), Cells(r + 1, c + blk))
        rng.Interior.Color = clr
    End If
    
End Sub
Now try it out. Just try entering one of those 8 codes, and you will see the coloring fill in.
 
Upvote 0
Solution
Hello Joe,

Your solution has completely solved my issue. Thank you very much.

Please help me to telling, how can I arrange this service for you ? (I don't know the rules of this forum). That was a real help for me.

Kind regards

Csaba
 
Upvote 0
csbaros said:
Your solution has completely solved my issue. Thank you very much.
Click to expand...
You are welcome. Glad it worked for you.

csbaros said:
Please help me to telling, how can I arrange this service for you ? (I don't know the rules of this forum). That was a real help for me.
Click to expand...
I am not sure what you mean. If you are talking about any sort of compensation, we do not accept any. Our help on this public forum is free!:)
Since my previous post solved your issue, I will mark it as the "Solution", but note that you can do that yourself in the future.
 
Upvote 0
Joe4 said:
I am not sure what you mean. If you are talking about any sort of compensation, we do not accept any. Our help on this public forum is free!:)
Since my previous post solved your issue, I will mark it as the "Solution", but note that you can do that yourself in the future.
Click to expand...
Yes, that's how I meant, some compensation....
Then thank you very much again and I will mark as a Solution next time.
Have nice day !
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Conditional Formatting for multiple cells based on other cell value
Replies
4
Views
250
Benong
B
W
  • Solved
Copying Conditional Formatting with relative values
Replies
9
Views
493
weeksy
W
M
  • Solved
The TOP 1 item in a row Conditional Format seems broken to me
Replies
5
Views
212
Mr_Phil
M
A
  • Question
Conditional Formatting but not copying it as such
Replies
1
Views
172
t0ny84
T
J
  • Question
Conditional formatting causing copying issues
Replies
3
Views
146
Fluff
Fluff

Forum statistics

Threads
1,214,650
Messages
6,120,736
Members
448,988
Latest member
BB_Unlv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top