VBA for conditional formatting

A

afrobea_r

Board Regular
Joined
Aug 16, 2015
Messages
76
Dear Excel Community,

I currently have a sheet with data resembling this:

[TABLE="width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Plan[/TD]
[TD] Days[/TD]
[/TR]
[TR]
[TD]Intended [/TD]
[TD] 13[/TD]
[/TR]
[TR]
[TD]Unintended[/TD]
[TD] 12[/TD]
[/TR]
[TR]
[TD]Intended [/TD]
[TD] 14[/TD]
[/TR]
[TR]
[TD]Intended[/TD]
[TD] 15[/TD]
[/TR]
[TR]
[TD]Intended[/TD]
[TD] 16[/TD]
[/TR]
[TR]
[TD]Unintended[/TD]
[TD] 8[/TD]
[/TR]
</tbody>[/TABLE]

As the conditional formatting function does not allow application of thick coloured borders, what kind of macro can I use to dynamically apply a thick red border to those cells in the days column only if their corresponding cell value in the plan column is 'unintended'? It must be dynamic as the row data may increase from time to time and certain values within existing rows will also change over time depending on info I have.

I have been stuck for some time. Any help is appreciated!! Thank you in advance!

Best Regards,
stuck
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
How about
Code: 
Sub AddBorders()
   Dim Rng As Range
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      For Each Rng In .SpecialCells(xlFormulas, xlErrors).Areas
         Rng.Resize(, 2).Borders.Weight = xlThick
         Rng.Resize(, 2).Borders.Color = 45678
      Next Rng
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
End Sub
 
Upvote 0
This is absolutely great sir! Thank u for this! As an additional interface that I just came across, how do I input a formula to cater for a situation whereby there are multiple columns like this:

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Plan[/TD]
[TD="class: xl63, width: 64"]Days[/TD]
[TD="class: xl63, width: 64"]Plan[/TD]
[TD="class: xl63, width: 64"]Days[/TD]
[TD="class: xl63, width: 64"]Plan[/TD]
[TD="class: xl63, width: 64"]Days[/TD]
[TD="class: xl63, width: 64"]Plan[/TD]
[/TR]
[TR]
[TD="class: xl63"]Intended[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"]Unintended[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63"]Intended[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]18[/TD]
[/TR]
[TR]
[TD="class: xl63"]Intended[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Intended[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]Unintended[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]Intended[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]19[/TD]
[/TR]
[TR]
[TD="class: xl63"]Unintended[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

And the border has to only appear for cell with values as well as tied to the conditional "unintended" in the plan column.

Subsequently, to clear the borders to revert to normal, other than recording a macro, is there a smarter way too?
 
Upvote 0
If the values are constants rather than formulae try
Code: 
Sub AddBorders()
   Dim Rng As Range
   Dim Ar As Areas
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
   For Each Rng In Ar
      Rng.Resize(, 7).SpecialCells(xlConstants).Borders.Weight = xlThick
      Rng.Resize(, 7).SpecialCells(xlConstants).Borders.Color = 45678
   Next Rng
End Sub
 
Upvote 0
Fluff said:
If the values are constants rather than formulae try
Code: 
Sub AddBorders()
   Dim Rng As Range
   Dim Ar As Areas
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
   For Each Rng In Ar
      Rng.Resize(, 7).SpecialCells(xlConstants).Borders.Weight = xlThick
      Rng.Resize(, 7).SpecialCells(xlConstants).Borders.Color = 45678
   Next Rng
End Sub
Click to expand...

Again, brilliant sir. And if the cell values are derived by formula rather constants? How can I make it as if it was the formula option in conditional formatting...
 
Upvote 0
Try
Code: 
Sub AddBorders()
   Dim rng As Range
   Dim Ar As Areas
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
   For Each rng In Ar
      With rng
         .Borders.Weight = xlThick
         .Borders.Color = 45678
         .Resize(, 7).SpecialCells(xlFormulas, 1).Borders.Weight = xlThick
         .Resize(, 7).SpecialCells(xlFormulas, 1).Borders.Color = 45678
      End With
   Next rng
End Sub
 
Upvote 0
Fluff said:
Try
Code: 
Sub AddBorders()
   Dim rng As Range
   Dim Ar As Areas
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
   For Each rng In Ar
      With rng
         .Borders.Weight = xlThick
         .Borders.Color = 45678
         .Resize(, 7).SpecialCells(xlFormulas, 1).Borders.Weight = xlThick
         .Resize(, 7).SpecialCells(xlFormulas, 1).Borders.Color = 45678
      End With
   Next rng
End Sub
Click to expand...


Hi Sir, have tried but when the cell values are derived via formula, the formatting does not apply. How do I recognize the derived value as a value and not a formula?
 
Upvote 0
afrobea_r said:
Hi Sir, have tried but when the cell values are derived via formula, the formatting does not apply. How do I recognize the derived value as a value and not a formula?
Click to expand...

Kindly ignore my previous. The formula works fine. However, while it recognizes numerical values derived from formulas, can it also recognize text values that are in the cells derived from formulas as well? eg concatenate.
 
Upvote 0
afrobea_r said:
Kindly ignore my previous. The formula works fine. However, while it recognizes numerical values derived from formulas, can it also recognize text values that are in the cells derived from formulas as well? eg concatenate.
Click to expand...

Pardon me once more, I wrongly communicated the last again. What I really meant was that some of the blank cells are derived via a formula and so they are not really blank in that sense. Will the VBA be able to circumvent this and recognize it as a true blank?
 
Upvote 0
The last code I supplied should do that already, although it won't recognise text values.
If you modify it to recognise text values as well as numerical values, then it will pick up any cell with a formula that returns "".
Unfortunately I know of no way round this, other than removing the formulae
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Summing Up only the Weekend Days from the Total
Replies
3
Views
158
Alex Blakenburg
A
R
  • Solved
Formatting time - minutes and seconds NOT time of day
Replies
2
Views
725
RodneyW
R
E
  • Question Question
Negative times and conditional formating.
Replies
2
Views
304
Ludwig
L
S
  • Question Question
Help with conditional formatting using a pivot table with dynamic ranges
Replies
1
Views
125
RoryA
RoryA
M
  • Solved
Time Function in Conditional Formatting
2
Replies
13
Views
85
Joe4
Joe4

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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