Conditional Format (VBA)
How can I compare to with a Chain in conditional format?
Function Color(name As String) Dim ws As Excel.Worksheet Set ws = ThisWorkbook.Worksheets(name) ws.Range("D9").Select With Selection .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=$S$41>0" With .FormatConditions(1) .Font.Bold = True .Interior.Color = 3 '.Size = 14 End With End With End Function
Formula1, I need to compare a String. For example,
S41 = "Select List"
See also questions close to this topic
Can't arrange items to the expected position
I've witten a script in vba to parse two categories from each container from a webpage. The scraper is able to parse them accordingly. The problem I'm facing at this moment is that I can't place these items across columns. If a column contains
views, the next column should contains
votesand so on. The way I'm expecting the result is more like:
column1 column2 column3 column4 9 views 0 vote 10 views -2
This is my script so far:
Sub CollectInfo() Const URL As String = "https://stackoverflow.com/questions/tagged/web-scraping" Dim Http As New XMLHTTP60, Html As New HTMLDocument Dim post As HTMLHtmlElement, R&, C& With Http .Open "GET", URL, False .send Html.body.innerHTML = .responseText End With R = 1 For Each post In Html.getElementsByClassName("question-summary") C = C + 1: Cells(R, C) = post.getElementsByClassName("views")(0).innerText Cells(R, C + 1) = post.getElementsByClassName("votes")(0).innerText Next post End Sub
The way I tried is definitely leading me to the wrong placing. How can I fix it to serve the purpose? Btw, I do not wish to go for the
Range("A1").offset(,1)") looping ;rather, I wanna stick to the way I tried above. Thanks.
I've ran into a syntax error in vba code for an Excel web query based on a table
I am trying to update a Macro that I recorded so that content from the lines in the spreadsheet will update the web query target but have run into a a few errors. I've sorted through several of them but the current issue is a Runtime error. The following is the code for my query:
Sub GetAllPkgInfo() Sheets("AllPkgs").Select Range("A2").Select Do Until ActiveCell.Value = "" Call PkgInfo1 Loop End Sub Sub PkgInfo1() ' ' PkgInfo1 Macro ' Import RHEL package information from RPMFind ' ' Keyboard Shortcut: Ctrl+Shift+D ' Selection.Copy Sheets("WebQuery").Select ActiveSheet.Cells.Clear Range("$G$1").Select ActiveSheet.Paste Application.CutCopyMode = False With ActiveSheet.QueryTables.Add( _ Connection:="URL;https://rpmfind.net/linux/rpm2html/search.php?query=" & _ Range("$G$1").Value, Destination:=Range("$A$1")) .CommandType = 0 .Name = "search.php?query=abrt_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "2" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveCell.Offset(1, -5).Range("A1").Select Selection.Copy Sheets("AllPkgs").Select ActiveCell.Offset(0, 1).Range("CombinedPackages[[#Headers],[Column1]]").Select ActiveSheet.Paste Sheets("WebQuery").Select ActiveCell.Cells.Select Application.CutCopyMode = False Selection.QueryTable.Delete Selection.ClearContents Sheets("AllPkgs").Select ActiveCell.Offset(1, -1).Range("CombinedPackages[[#Headers],[Column1]]").Select End Sub
The error message I receive states "Runtime error '1004': Paste Method of Worksheet Class Failed" and highlights the first code instance of
Can anybody help me get straightened out please?
vba range of values pivot
I have a pivot table with values filtered by years months and days. I managed to create a vba code that filters according to 4 variablers+ year, month, day 1 or day2.
Now the problem is that I would like to filter by a range of days. Let's say I want year=2018, month=June, day1=10 and day2=15. With the current code the pivot will be filtered by 10-june-2018 and 15-june-2018. Instead, I would like to filter it like: FROM 10-june-2018 TO 15-june-2018
Sub PivotLast2D() Application.ScreenUpdating = False Sheets("Type10D").Visible = True Sheets("Type10D").Select Dim day1 As String Dim day2 As String Dim pi As PivotItem Dim y As String Dim z As String day1 = Sheet6.Range("G16").Value day2 = Sheet6.Range("G18").Value y = Sheet6.Range("F14").Value z = Sheet6.Range("F16").Value Sheets("Type10D").Select ActiveSheet.PivotTables("PivotTable2").ClearAllFilters For Each pi In ActiveSheet.PivotTables("PivotTable2").PivotFields("Years").PivotItems pi.Visible = (pi.Name = y) Next For Each pi In ActiveSheet.PivotTables("PivotTable2").PivotFields("Month").PivotItems pi.Visible = (pi.Name = z) Next For Each pi In ActiveSheet.PivotTables("PivotTable2").PivotFields("Day").PivotItems pi.Visible = (pi.Name = day1 Or pi.Name = day2) Next Sheets("Type10D").Visible = False Worksheets("Statistics").Activate Application.ScreenUpdating = True End Sub
I tried to change the line as:
For i = day1 To day2 For Each pi In ActiveSheet.PivotTables("PivotTable2").PivotFields("Day").PivotItems j = i pi.Visible = (pi.Name = j) Next pi Next i
But it gives me error. The funny thing is, if I type a number in j = i, such as j=12, then the code works. But if i put the i instead of the number, the code gives error "Unable to set the visible property of the pivotitem class"