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

[bobby786]

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

 

[DanteAmor]

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.

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]

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]

@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.

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.

?

 

[bobby786]

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]

Thanks

 

[DanteAmor]

@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]

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