• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Conditional formatting depending on the figures of a fix column|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Conditional formatting depending on the figures of a fix column|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Conditional formatting depending on…
sp_PrintTopic sp_TopicIcon
Conditional formatting depending on the figures of a fix column
Avatar
Cristina González
Member
Members
Level 0
Forum Posts: 29
Member Since:
November 16, 2019
sp_UserOfflineSmall Offline
1
February 17, 2020 - 4:41 am
sp_Permalink sp_Print

Hello,

I would like to know if there is a solution to the problem I explain in the image below.

Conditional-Formatting.PNGImage Enlarger

Thanks in advance,

Cristina González

sp_PlupAttachments Attachments
  • sp_PlupImage Conditional-Formatting.PNG (280 KB)
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
February 17, 2020 - 5:57 am
sp_Permalink sp_Print

Hi Cristina
As a first attempt

I have written a small macro that when press will check the dates in row 6 and hide anything that is older than today, it then updates the date in g6 and clears all the cells below.

I can’t work out what the basis is for the conditional formatting as some cells have a number others have a sum of 2 or more numbers - if you can let me know when you want the cells to be green I can update further.

Have a look at the attached and let me know if i am on the right track

I was also not sure of all the other workbooks, if they were relevant?

Purfleet

Avatar
Cristina González
Member
Members
Level 0
Forum Posts: 29
Member Since:
November 16, 2019
sp_UserOfflineSmall Offline
3
February 19, 2020 - 5:04 am
sp_Permalink sp_Print

Hi Purfleet,

I haven't thought of a macro to hide the columns but this is great, you are on the right track, yes. Now, what I would like is to coloured the cells in the red area such as I explain in the picture. 

Doubt.PNGImage Enlarger

 

Thanks a lot for your feedback,

Cristina 

sp_PlupAttachments Attachments
  • sp_PlupImage Doubt.PNG (259 KB)
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
February 19, 2020 - 8:58 pm
sp_Permalink sp_Print

the conditional formatting wasn't to bad to work out, however i couldn't get it to work with an expanding range for additional dates.

So i cheated and did the comparison and formatting in VBA

Sub UpdateGrid()

Dim i As Integer
Dim td As Date
Dim dateR As Range
Dim dateC As Range
Dim LastDateColAdd As String
Dim LastDateColNum As Integer
Dim StartDateColAdd As String
Dim CondFormatR As Range
Dim CondFormatC As Range

td = Now()

'Finds last column cell address
LastDateColAdd = Cells(6, Columns.Count).End(xlToLeft).Address
'Finds last column number
LastDateColNum = Cells(6, Columns.Count).End(xlToLeft).Column

'Finds the first column to calc
StartDateColAdd = Left(Range("6:6").Find(what:=Format(td, "DD-MMm"), LookIn:=xlValues).Address, 3)

'clears anything in row 4 (temp formula)
Range("4:4").ClearContents

'Clears any colour formatting
With Range("7:14")
.Font.Color = vbBlack
.Interior.Color = xlNone
End With

'loops around rows 7 to 14
For i = 7 To 14

'formula for each row compared to columns g
Range("H4:" & Left(LastDateColAdd, 3) & 4) = "=IF(H6<TODAY(),"""",IF(SUMIF($H$6:H6,"">=""&TODAY(),$H$" & i & ":H" & i & ")<=$G$" & i & ",""Green"",""Red""))"

'set range for formatting
Set CondFormatR = Range(StartDateColAdd & i & ":" & Left(LastDateColAdd, 3) & i)

'loops through each cell and comapares to formula and either makes the cell green or the writing red
For Each CondFormatC In CondFormatR
If CondFormatC.Offset(4 - i, 0) = "Green" Then
CondFormatC.Interior.Color = vbGreen
End If

If CondFormatC.Offset(4 - i, 0) = "Red" Then
CondFormatC.Font.Color = vbRed
End If
Next CondFormatC

Next i

'Numbers for column G for testing!
'2400
'780
'702
'234
'2262
'1248
'4968
'5184

'hides Columns
'Sets date range
Set dateR = Range("h6:" & LastDateColAdd)

'Loops though all cells and compares date to hide
For Each dateC In dateR
If dateC < td - 1 Then
dateC.EntireColumn.Hidden = True
End If
Next dateC

'Updates G6 heading with todays date
Range("g6") = "Stock Day " & Format(td, "DD/MM")

'Clears data in column G
Range("g7:g14").ClearContents

'clears anything in row 4 (temp formula)
Range("4:4").ClearContents

'Ask users to update quantities
MsgBox "Please enter stock quantities in rows 7 to 14", vbInformation

End Sub

Try the attached and let me know what you think

Purfleet

Avatar
Cristina González
Member
Members
Level 0
Forum Posts: 29
Member Since:
November 16, 2019
sp_UserOfflineSmall Offline
5
February 22, 2020 - 10:22 pm
sp_Permalink sp_Print

Hi Purfleet,

I had to remove lines:

'Clears data in column G
Range("g7:g14").ClearContents

and

'Ask users to update quantities
MsgBox "Please enter stock quantities in rows 7 to 14", vbInformation

because cells were coloured according to the stock I wrote incolumn "G" but then, it dissappeared and a I were asked to enter the figures in column "G" again. Removing this two lines, it works perfect.

I would like to do the same with the below area of the document in which there are the same info but instead of showing the quantity of parts, shows the number of pallets, is it possible to add this?

And the most important question, I want to learn how to do this, what do you recommend?

Thanks a lot for your support,

Cristina

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
6
February 23, 2020 - 4:35 am
sp_Permalink sp_Print

Yes, thinking about it you would add the numbers and then run the macro whereas I was thinking it would be the other way away, which is a bit stupid in hindsight.

I will try to have a look at the other table later on

With regards to learning how to use VBA - you have already made a start by being able to remove the lines above.

There is so much information on this site (https://www.myonlinetraininghu...../excel-vba) and youtube but a lot depends on how best you learn. Personally, I like watching the videos on youtube and then trying to recreate it on my own and then working out how to use it in my work.

Joining this message board has helped a lot as i can see what kind of issues people have, research them and try to work out how best to resolve

Purfleet

Avatar
Cristina González
Member
Members
Level 0
Forum Posts: 29
Member Since:
November 16, 2019
sp_UserOfflineSmall Offline
7
February 23, 2020 - 10:29 pm
sp_Permalink sp_Print

Hi Purfleet,

I got it!!! I got to do the same with the table below, the one that shows the number of pallets. I copied the code and paid attention to the rows they referred to and changed them accordingly. 

I will follow your advice and will start to see videos and to read the posts here in this website. I've started to think how I can improve the excel files I use everyday in my job. 

I am really thankful for your help. Thanks so much and have a nice Sunday 🙂

Cristina

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
8
February 24, 2020 - 3:51 am
sp_Permalink sp_Print

Brilliant - looks good and is very satisfying when you can get it to work (and very frustrating when you can’t!)

Avatar
Cristina González
Member
Members
Level 0
Forum Posts: 29
Member Since:
November 16, 2019
sp_UserOfflineSmall Offline
9
March 1, 2020 - 10:08 pm
sp_Permalink sp_Print

Hi Purfleet,

The file has been working correctly at work but this morning I've tried to read something about VBA and opened the file in my computer to see the code. Unfortunaly, to my surprise, the bottom doesn't work and a message appears saying:

"Se ha producido el error "91" en tiempo de ejecución: variable de objeto o bloque with no establecido"

in English

"Error "91" has been produced in execution time: object variable or with block is not established".

In the VBA editor I see that the line affected is this one:

StartDateColAdd = Left(Range("6:6").Find(what:=Format(td, "DD-MMm"), LookIn:=xlValues).Address, 3)

but I am unable to find out what is wrong...

I've thought that maybe the reason is that today is a new month, is it possible? why it doesn't work if it has been working all the time? it is quite frustrating, to be hones 🙁

Thanks,

Cristina

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
10
March 3, 2020 - 6:04 am
sp_Permalink sp_Print

I have no idea why it has stopped working, sorry.

As a work around i have put in a check on each cell in the range to test the date rather than a find - its a bit slower but on your fairly small data sets it should be okay.

For Each CC In DateSearchRange
If CC = td Then
StartDateColAdd = CC.Address
Exit For
End If
Next CC

Test the attached and let me know how you get on.

Purfleet

Avatar
Cristina González
Member
Members
Level 0
Forum Posts: 29
Member Since:
November 16, 2019
sp_UserOfflineSmall Offline
11
March 3, 2020 - 7:49 am
sp_Permalink sp_Print

Hi Purfleet,

It has started to work again!!!

OMG, I have so much to learn... everything is new and this is an advance level macro, at least for me.

Thanks so much again 🙂

Cristina

The following users say thank you to Cristina González for this useful post:

Purfleet
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Louis Muti
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Blair Gallagher
Brandi Taylor
Hafiz Ihsan Qadir
Gontran Bage
adolfo casanova
Annestine Johnpulle
Priscila Campbell
Jeff Mikles
Aaron Butler
Maurice Petterlin
Forum Stats:
Groups: 3
Forums: 24
Topics: 6369
Posts: 27852

 

Member Stats:
Guest Posters: 49
Members: 32359
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.