Complete Rows Highlighted when there is specific data in cell with specific color & Latest Balance Get Auto Color

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Dear All experts ,

Please forgive me for my bad English as i am not native English guy. I am assigned to carry on some work from my company and i was given a excel sheet which was probably made by my previous colleague , since i am not very familiar with VBA for excel so i am looking for help from your guys.

Below google drive link to download sample sheet

https://drive.google.com/file/d/1qqu...ew?usp=sharing

Problem # 1- In sample Worksheet i have sheet Work and Manual , where as Manual sheet i just want to show how i am doing it manually , in which Col B "BANK NAME" is listing all the banks and it will show the running balance of that bank , but as you can see i have multiple banks so i have to assign each bank a color ( i want every time a new bank name is entered it will get a color highlight for whole row , right now i am doing it manually which is eating a lot of time and often i make mistakes in that. So it is possible in excel world to make it automatic.

Problem # 2 - Col H There is formula in place to show the latest balance of that particular bank and gets it highlighted with Yellow , so in order to find the last balance of each bank i can just look for this yellow color , but somehow it is not working if someone can help me get it fixed. P.S again the formula was not done by me.

I am trying to learn the great world of excel and looking forward for help/guide in my journey

EDIT: i have also attached the excel file here as attachment in case someone does not like to click on link.

Thank you in Advance.
Regards
Bobby
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,620
Office Version
  1. 2007
Platform
  1. Windows
Run the following macro.
In column B the macro highlights in yellow the last balance of each bank. That way you can filter in column B by color.

VBA Code:
Sub lastest_balance()
  Dim r As Range
  Dim lr As Long, i As Long
  Dim dic As Object, itm As Variant
  
  lr = Range("B" & Rows.Count).End(3).Row
  Set r = Range("B" & lr)
  Set dic = CreateObject("Scripting.Dictionary")
  
  Range("B:B").Interior.ColorIndex = xlNone
  
  For i = 4 To lr
    If Range("B" & i).Value <> "" Then dic(Range("B" & i).Value) = i
  Next
  
  For Each itm In dic.items
    Set r = Union(r, Range("B" & itm))
  Next
  
  r.Interior.ColorIndex = 6
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (lastest_balance) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,541
Office Version
  1. 365
Platform
  1. Windows
Cross posted Highlights Rows when there is specific data in cell & Latest Balance Get Auto Color

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.
 

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
@DanteAmor
Thank you for your reply. Is there any method where i dont have to run the macro you provided every time when there is new/edit entries?

and Problem# 1 is not possible in excel ?

Regards

Run the following macro.
In column B the macro highlights in yellow the last balance of each bank. That way you can filter in column B by color.

VBA Code:
Sub lastest_balance()
  Dim r As Range
  Dim lr As Long, i As Long
  Dim dic As Object, itm As Variant

  lr = Range("B" & Rows.Count).End(3).Row
  Set r = Range("B" & lr)
  Set dic = CreateObject("Scripting.Dictionary")

  Range("B:B").Interior.ColorIndex = xlNone

  For i = 4 To lr
    If Range("B" & i).Value <> "" Then dic(Range("B" & i).Value) = i
  Next

  For Each itm In dic.items
    Set r = Union(r, Range("B" & itm))
  Next

  r.Interior.ColorIndex = 6
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (lastest_balance) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

?
 
Last edited:

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Cross posted Highlights Rows when there is specific data in cell & Latest Balance Get Auto Color

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.
i am very sorry for this , i was not aware of it , it wont happen next time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,541
Office Version
  1. 365
Platform
  1. Windows

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,620
Office Version
  1. 2007
Platform
  1. Windows
@DanteAmor
Thank you for your reply. Is there any method where i dont have to run the macro you provided every time when there is new/edit entries?
and Problem# 1 is not possible in excel ?

With conditional formatting.
- Select column B.
- Menú: Conditional Formatting
- New rule
- Formula:

=COUNTIF(B1:$B$1000000,B1)=1

- Format, select yellow color.
- Ok, Ok.
 

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
With conditional formatting.
- Select column B.
- Menú: Conditional Formatting
- New rule
- Formula:

=COUNTIF(B1:$B$1000000,B1)=1

- Format, select yellow color.
- Ok, Ok.
Thank you very much it does solve my Problem # 2 ,
now i want to apply the same theory on whole ROW to get my Problem #1 fixed as well , can you tell me how to achieve that ? but keeping the Col B conditional formatting intact , whole ROWS will get its corresponding bank color , like say Bank1=Blue but Col B of that Bank1 if its latest balance still should get the Yellow otherwise the whole row is Blue color , Bank2 represent Orange color and so on..

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,129,510
Messages
5,636,742
Members
416,938
Latest member
sc58963

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
Top